A schema is essentially just another database object, a container for other objects that makes it easier to manage groups of objects in complex databases. But schemas have important security features as well. In this stairway level you’ll learn how you can give principals access to groups of objects by assigning permissions on schemas instead of individual tables, code modules, and other objects. You’ll also learn about the benefits of user-schema separation and how it can increase object security, and how using default schemas for users and groups can simplify object access management and security.

Schemas with Roles and Permissions

The relationship of schemas with roles and permissions is an important security concept in SQL Server. A fully qualified database object name consists of four parts:

  server.database.schema.object

Usually you’ll just need to refer to objects in the current database context by using the schema and object names. A schema is a collection of objects, such as tables and code modules, as shown in Figure 5.1. This organizational structure simplifies user management, particularly when you have to change ownership of objects. But more importantly for security, it simplifies permissions management.

Figure 5.1: A sample schema that contains database objects and is owned by a user.

You can assign permissions on a schema that apply to all objects in the schema. For example, if you assign SELECT permission on DogSchema to a principal, all the tables in that schema will have that permission. Like all user-defined database objects, a schema has an owner that has complete control over the object.

Setting permissions individually on objects within a schema is always an option, but if you’ve designed the schemas in a database well, in some sort of functional categories that make sense for the database, you can set permissions on the schema and have them apply to dozens if not hundreds of objects. Best of all, the permissions you assign on a schema apply automatically to any future objects you add to the schema. Continuing the SELECT example, if a year from now you add DogTable4 to DogSchema, all principals with SELECT permission on the schema automatically have that permission on the new table.

Multiple users and roles can have the same default schema, and if a principal has no default schema set, SQL Server attempts to find or create the object in the dbo schema.

Now you’ll see how you can use schemas to assign permissions to objects. Use the following steps to grant SELECT, UPDATE, DELETE, and INSERT permissions on the Purchasing schema to the DataEntry user-defined database role in the AdventureWorks2012 database. The code in Level 4 created that role; if you didn’t create it there, execute the code in Listing 5.1 in Management Studio.

USE AdventureWorks2012;
GO
CREATE ROLE DataEntry AUTHORIZATION dbo;

Listing 5.1: Creating the DataEntry role in AdventureWorks2012.

Then follow these steps in Management Studio to use the graphical tools to assign the needed permissions.

1. Expand the Security node in Object Explorer for the AdventureWorks2012 database. Drill down to Roles|Database Roles|DataEntry.
2. Right-click the DataEntry role and select Properties from the pop-up menu. Select the Securables page in the Database Role Properties dialog box. If you followed along the steps in Level 4, you should see the tables and stored procedures that you earlier assigned permissions for to this role.
3. Click the Search button to open the Add Objects dialog box.
4. In the Add Objects dialog box, select the All objects of the types option, as shown in Figure 5.2, and click OK to open the Select Object Types dialog box.

Figure 5.2: Selecting to list all objects of a specific type.

5. In the Select Object Types dialog box, scroll down to the Schemas item and select the check box next to it. The dialog box should look like Figure 5.3. Click OK to save the selection and close the dialog box.

Figure 5.3: Selecting objects of type Schema.

6. Back in the Database Role Properties dialog box, scroll down the Securables list in the upper part of the page and click on the Purchasing schema. This displays the available permissions in the lower part of the page.
7. Select the Delete, Insert, Select, and Update permissions in the Grant column on the Explicit tab in the Permissions for Purchasing section of the form. The Database Role Properties dialog box should look like Figure 5.4.

Figure 5.4: Setting table access permissions for the Purchasing schema.

8. Click OK to commit the changes.

Now all members of the DataEntry role have SELECT, UPDATE, DELETE, and INSERT permissions on all tables within the Purchasing schema in AdventureWorks2012. An exception occurs only if any individual member of the role was denied any of the permissions. This prevents them from inheriting the denied permission through membership in the role.

Alternatively, you can grant these permissions to a schema using the T-SQL code in Listing 5.2.

GRANT DELETE ON SCHEMA::Purchasing TO DataEntry;
GRANT INSERT ON SCHEMA::Purchasing TO DataEntry;
GRANT SELECT ON SCHEMA::Purchasing TO DataEntry;
GRANT UPDATE ON SCHEMA::Purchasing TO DataEntry;
GO

Listing 5.2: Code to add DELETE, INSERT, SELECT, and UPDATE permissions to the Purchasing schema.

These techniques show that you can create different schemas, put different objects in each schema, and then assign permissions on the schema. This saves all the work of assigning permissions on the individual tables. If you grant the permissions to a role, as we did here with the DataEntry role, you can effectively assign permissions to many principals—all the members of the role—all at once. This lets you partition your database, such as by departments as implemented in AdventureWorks2012, and simplifies how you design and implement database security.

Default Schemas

As defined in the SQL-99 specification, a schema is essentially a container for objects in the database. It then may, in turn, be owned by a principal, as shown in Figure 5.5 (which is the same as Figure 5.1). One of the benefits of using schemas as a database object container is when Carol leaves the company. Instead of changing the ownership of hundreds or thousands of objects that Carol owns, admins only have to change the ownership of those schemas, each of which could have thousands of objects. This approach is much cleaner, much easier, and much more secure.

Figure 5.5: A DogSchema schema owned by Carol.

SQL Server lets you assign default schemas for users and for groups. The ability to set default schemas is both a convenience and has some important operational benefits. In particular, it removes some of the ambiguities when naming and accessing objects.

Default Schemas for Users

SQL Server doesn’t automatically create a schema with the same name as the user when you create a user. Instead you have to explicitly create a schema, assign ownership to a user, then create and add objects to that schema. You can (and usually should) assign a default schema to a user so that all objects the user creates—and doesn’t explicitly assign to another schema—become part of the default schema.

The code file that accompanies this Level shows how all this works, exploring what happens when a user doesn’t have a default schema set. I’ll explain what is going on here at each step, but you might want to follow along as well in the code file to better see the flow of what’s happening. And, of course, try it out yourself! If you want to see what happens, execute each chunk of code in each listing as a unit.

The code in Listing 5.3 does some of the setup needed for the demonstrations. You may have already created the login carol in Level 3, so the code first drops the login if it exists, to start afresh with different permissions. After creating the DefaultSchema database and changing the database context to it, the code creates the login carol, maps it to the user carol in the database, and grants it the ability to create tables. It then changes the execution context to the new user carol.

IF suser_sid('carol') IS NOT NULL DROP LOGIN carol;
GO
CREATE DATABASE DefaultSchema;
GO
USE DefaultSchema;
GO
CREATE LOGIN carol WITH PASSWORD = 'crolPWD123%%%';
CREATE USER carol FOR LOGIN carol;
GRANT CREATE TABLE TO carol;
EXECUTE AS LOGIN = 'carol';
GO

Listing 5.3: Code to create the DefaultSchema database and set up the carol user.

The code next attempts to create a new table1, as shown in Listing 5.4. But when the pervious code created carol it didn’t assign a default schema. SQL Server attempts to use the dbo schema, which is the default fallback schema. But Carol doesn’t have ownership rights in the database so she can’t create objects in the dbo schema.

CREATE TABLE table1 (tID int);

Listing 5.4: Attempt to create table1 in the carol execution context.

Since carol doesn’t have the needed permissions the CREATE TABLE statement fails with the following error message. In this case the problem of the two possibilities suggested in the message is that carol doesn’t have permission in the dbo schema.

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.

The code shown in Listing 5.5 reverts to the original admin login that started this session, then creates a schema and gives ownership to user carol. You’ll see the AUTHORIZATION clause a lot in SQL Server because it lets you assign ownership in the same statement that creates or alters an object.

REVERT;
CREATE SCHEMA DogSchema AUTHORIZATION carol;

Listing 5.5: Code to create a DogSchema owned by carol.

The code then once again changes the execution context to carol and attempts again to create table1. But it fails again! The problem now is that just because a user owns a schema doesn’t mean that it’s the user’s default schema. A user could own hundreds of schemas and SQL Server shouldn’t be responsible for picking one to be the default. But what does finally work is creating the table to be explicitly contained within the schema. The statement in Listing 5.6 explicitly creates table1 in the DogSchema, which finally works.

CREATE TABLE DogSchema.table1 (tID int);

Listing 5.6: Creating a table and explicitly specifying the schema.

Success at last!

The second attempt to create the table, once DogSchema existed, would have succeeded had the code assigned a default schema, either when initially creating the user or by later altering it, as shown in Listing 5.7.

CREATE USER carol FOR LOGIN carol WITH DEFAULT_SCHEMA = DogSchema;
-- or
ALTER USER carol WITH DEFAULT_SCHEMA = DogSchema;

Listing 5.7: Code to set a default schema for carol either when creating the user or later altering the user.

If you execute the ALTER USER statement to set the default schema for carol, you can then execute the code in Listing 5.8 to successfully create table2 without specifying a schema. The CREATE TABLE statement will create a DogSchema.table2 table. Since DogSchema is carol’s default schema, that’s the schema SQL Server uses.

EXECUTE AS LOGIN = 'carol';
GO
CREATE TABLE table2 (tID int);
GO
SELECT * FROM table2;
REVERT;

Listing 5.8: Creating table2 without specifying a schema, after setting carol’s default schema.

Another interesting observation is that after you revert to your own security context—using the REVERT statement—you can’t execute the code in Listing 5.9. Unless you’ve set your own default schema to DogSchema, SQL Server will look for a table called dbo.table2, which doesn’t exist.

SELECT * FROM table2;

Listing 5.9: This code will cause an error when not run in carol’s execution context.

Instead, you need to explicitly use the schema to identify the table you want to read data from, as in the code in Listing 5.10. This code succeeds, and returns the contents of DogSchema.table2 (which is empty at this point).

SELECT * FROM DogSchema.table2;

Listing 5.10: Performing a SELECT when the user’s default schema is not set to DogSchema.

The separation of users and schemas in SQL Server is yet another way that you can keep tighter control over the security architecture of your databases and applications. It most certainly makes it easier to administer a database and SQL Server. You shouldn’t need to have dbo own everything anymore, as was common in versions before SQL Server 2005.

Default Schemas for Groups

Default schemas for users, introduced way back in SQL Server 2005, solved a problem with the level of certainty that querying, creating objects, and other operations would use the correct object in the correct schema. But a problematic side effect of these default schemas was that you could easily end up with lots of implicitly created schemas lying around a database. Default schemas for Windows groups, introduced in SQL Server 2012, solves these problems.

Use the following steps to explore the potential problems of having default schemas only for users. The steps assume that the local instance of Windows has a group named DBAs and a JoeStairway login that is a member of that group. You’ll also need to change the machine name from Marathon in the sample code (which is the name of the machine I developed this code on). Finally, the DefaultSchema database should already exist; code to do that is in the sample code file if you didn’t do that earlier in the Level.

Once again, you can follow along in the accompanying code file and the explanations here. The instructions are a little tricky because you’ll be working with two instances of Management Studio. Again execute each chunk of code in each listing as a unit.

1. After changing the database context to DefaultSchema, the code shown in Listing 5.11 creates a login mapped to the Windows DBAs group and a user mapped to that login, then creates a DBAs role and adds the DataAdmins user to it.

CREATE LOGIN [Marathon\DBAs] FROM WINDOWS;
CREATE USER DataAdmins FROM LOGIN [Marathon\DBAs];
CREATE ROLE DBAs;
ALTER ROLE DBAs ADD MEMBER DataAdmins;

Listing 5.11: Creating the login, user, and role needed by the following code.

2. The next chunk of code, shown in Listing 5.12, grants the CREATE TABLE and CONTROL permissions on the DogSchema scheme to the DBAs role.

GRANT CREATE TABLE TO DBAs;
GRANT CONTROL ON SCHEMA::DogSchema TO DBAs;

Listing 5.12: Granting permissions to the DBAs role.

3. Now go open another instance of Management Studio as JoeStairway. Do this by holding down the SHIFT key as you right-click SQL Server Management Studio in the Windows Start menu. Select Run as different user from the pop-up menu, and enter Joe’s login credentials when Windows prompts you for them.
4. In the Management Studio Connect to Server dialog box, use Windows Authentication and log in as JoeStairway, as shown in Figure 5.6. Click Connect to connect. You’re now running Management Studio as JoeStairway.

Figure 5.6. Logging in to SQL Server as JoeStairway.

5. Open the code file in this new instance of Management Studio, and go to the first CREATE TABLE statement in the Explore default schema for group section, after the code you already executed in the other instance of Management Studio. Change the database context to DefaultSchema, using the dropdown list in the toolbar.
6. Execute the line of code in Listing 5.13. This successfully creates table1, but what schema is it a part of?

CREATE TABLE table1 (tID int)

Listing 5.13: CREATE TABLE statement, executed as JoeStairway.

7. Expand the Tables, Users, and Schemas node of the DefaultSchema database in Object Explorer (the latter two are under the Security node). As you can see in Figure 5.7, the statement created a table called MARATHON\JoeStairway.table1, a database user named MARATHON\JoeStairway, and a schema named MARATHON\JoeStairway. That’s an awful lot of detritus from creating a single table in a schema that we didn’t intend!

Figure 5.7. Effects of creating a table with no default schema.

8. Return to the original instance of Management Studio, where you are logged in as an admin. Delete the wayward table, schema, and user, in that order, from Object Explorer.
9. Still in the original instance of Management Studio, execute the line of code in Listing 5.14 to set a default schema for the DataAdmins user, using the DogSchema you created earlier in the chapter.

ALTER USER DataAdmins WITH DEFAULT_SCHEMA = DogSchema;

Listing 5.14: Code to set a default schema for the DataAdmins role.

10. Return once again to JoeStairway’s instance of Management Studio and execute the line of code in Listing 5.15 to again create a new table. This time the code creates a table named DogSchema.table3, and JoeStairway is not added as a user, nor is a schema added with that name.

CREATE TABLE table3 (tID int)

Listing 5.15: Create table3, which now will be in the DogSchema schema.

You can also set a user’s default schema in the user’s Database User dialog box, as shown in Figure 5.8.

Figure 5.8: Using the Database User dialog box to set a user’s default schema.

SQL Server 2012 added the ability to define default schemas for groups as well as users, which goes a long way toward solving the problem with default schemas, as well as making security management easier. For the same reason you create users without any permissions, then add them to groups with the permissions they need, you can specify a default schema for groups that apply to its members, instead of to each user. As with users, you specify the default schema for a group either using the CREATE USER or ALTER USER statement.

Summary

Although schemas are a nice feature of SQL Server for managing database objects into convenient containers, they also provide important security features as well. By setting permissions on a schema rather than on individual objects it contains, you can far more easily manage the permissions scheme for the database. This is particularly important when you have a number of principals that you need to grant permissions to.

Be sure to always assign default schemas to both users and groups, so that you avoid unintentional object creation as well as to simplify both code and database maintenance. With the addition of the ability to set default schemas for groups, Microsoft has fleshed out the benefits of schemas for security.