Schemas

Schemas were a feature in SQL Server 2000 that weren't emphasized as much, but in SQL Server 2005, they're an integral part of the database engine. Schemas allow you to group database objects into a logical group for security, ease of use, and manageability. Whether you have created custom schemas or not, it's a best practice to use schema names in your queries. If you were to query the Salary table in the HumanResource schema, it may look like this:

 SELECT FirstName, LastName, Salary, StartDate, EndDate FROM HumanResource.Salary 

Since the HumanResource schema contains information that should be considered more secure, you can allow a user to see all tables with the exception of tables in that schema. You could also give a user a schema that the user owned and could create tables inside it without interfering with the other tables in the database. This is because the user could create tables inside the schema that would be named differently. In effect, the table name Employees is different from the table name SchemaName.Employees. If a schema is not specified, it is implied that you want the dbo schema. So the Employees table in the last example without a schema name was actually dbo.Employees.

Schema Example

Let's try an example on how to use schemas to both compartmentalize your tables and secure them. First, create an empty database called SchemaExample. Next, create a login named SchemaExampleLogin and a password of schemapass08. Give the login public access to the SchemaExample database, and make it the login's default database. Last, grant the login rights at a user-level in the SchemaExample database rights to create tables. You'll have to go to the User Properties dialog box to do this under the Securables page. You can use the steps covered in the login section of this chapter to do this, or you can use the following script, downloadable from this book's page on www.wrox.com.

 USE [master] GO CREATE LOGIN [SchemaExampleLogin] WITH PASSWORD=N'schemapass08', DEFAULT_DATABASE=[SchemaExample], CHECK_EXPIRATION=ON, CHECK_POLICY=ON GO USE [SchemaExample] GO CREATE USER [SchemaExampleLogin] FOR LOGIN [SchemaExampleLogin] GRANT CREATE TABLE TO [SchemaExampleLogin] 

With the preparation now complete, it's time to create a schema. In Management Studio, connect to the SchemaExample database. Right-click Schemas under the Security tree and select New Schema. In the New Schema dialog box (shown in Figure 9-8), type the name TestSchema for the schema name and make the schema owner the SchemaExampleLogin that you created earlier. Alternatively, you can use the CREATE SCHEMA syntax as shown here to perform the same action:

 CREATE SCHEMA [TestSchema] AUTHORIZATION [SchemaExampleLogin] 

Figure 9-8

Now that the schema has been created, you can try to create a few tables using Management Studio and through T-SQL. Right-click Tables in Management Studio under the SchemaTest database and select New Table. In the Table Designer, create one column called Column1 with an integer data type, as shown in Figure 9-9. Go to the Properties Window of the Table Designer and select TestSchema from the Schema drop-down box. If the Properties Window is not showing, select Properties Window from the View menu (or hit F4). Also in the Properties Window, type TableDesignerTest for the table name. Click Save and close the Table Designer.

Figure 9-9

In Management Studio, expand the tables folder (you may have to right-click and select Refresh to see the table). You should see your newly created table there, and it should be titled TestSchema.TableDesignerTest.

Next, open a new query while connecting with the login of SchemaExampleLogin and a password of schemapass08. Ensure that the database that you're connected to is SchemaExample. Next, try to create a simple test table by using the following syntax:

 create table TestSchema.TestTable (column1 int) 

Next, try to create another table in the dbo schema by using the following syntax. Remember that since you didn't specify a schema in this syntax, the table will be created in the dbo schema.

 create table TestTableProblem (column1 int) 

Because you don't have rights to create tables in the dbo schema, you will receive the following error message.

 Msg 2760, Level 16, State 1, Line 1 The specified schema name "dbo" either does not exist or you do not have permission to use it. 

To create the table, you must explicitly grant the user rights to create the table in the dbo schema. To do this, you can use Management Studio to grant the user ALTER rights to the dbo schema in the Securables page. You can also use the following syntax to perform the same action. You will need to be logged in as a user with higher authority to do this, though.

 GRANT ALTER ON SCHEMA::[dbo] TO [SchemaExampleLogin] GO 

Once the user has been granted ALTER rights to the schema, he or she will now be able to create the table. This simple example shows you how you can use schemas to control the user's access through grouping tables. Schemas are also handy from a usability perspective. By naming a table HumanResource.Contact, it has an implied meaning that these are human resource contacts, not sales leads.

Changing Ownership

Once a schema has tables inside of it, it cannot be dropped until all the objects have been moved out of it. SQL Server does not want to implicitly assume that you mean to move the tables from the TestSchema schema to the dbo schema. Instead of implicitly doing this, the command will fail until the administrator or owner explicitly moves the tables. The error the user would receive would look like this (where TableDesignerTest is the first table in the schema):

 Msg 3729, Level 16, State 1, Line 2 Cannot drop schema 'SchemaName' because it is being referenced by object 'TableDesignerTest'. 

You also cannot drop any login or user that owns a schema. If you were to try to delete a user that owned a schema, you would receive an error like this:

 Msg 15138, Level 16, State 1, Line 1 The database principal owns a schema in the database, and cannot be dropped. 

The way around this is to create a new schema (or use an existing one) and move all the objects in the old schema to the new schema. You can do this by using the ALTER SCHEMA syntax followed by the TRANSFER keyword. The following syntax will create a new schema called SecondSchema and then move the TestSchema.TestTable table over to it. Then you could delete the empty schema.

 CREATE SCHEMA SecondSchema GO ALTER SCHEMA SecondSchema TRANSFER TestSchema.TestTable GO