SQL Server 2005 Schemas -- separate users from objects

SQL Server 2005 introduces a world of new features and in my first article for Windows IT Pro News UK I gave a very high-level overview about some of them. In my previous article I wrote about SQL Server Service broker, the new messaging infrastructure in SQL Server 2005. In this article, I will take a look at a new feature in the SQL Server called schemas.

The fully qualified name of a database object in SQL Server 2000 (and earlier versions) was like the following:

server-name.database-name.owner-name.object-name.

So, for example, if the user Bob in the database db1 in the server serv1 created a table called tab1, the fully qualified name would be:

serv1.db1.Bob.tab1.

In other words, the creator was tightly coupled to the object itself. Subsequently, if we for some reason wanted to delete Bob from the database (DROP USER BOB), we could not do that without first dropping the objects owned by Bob or change the owner of the objects. Neither of these options is really appealing as we probably have code referencing the objects and we would then need to change our code.

We can see a schema as a collection of database entities, sharing the same schema name. If you are familiar with the Common Language Runtime, you can draw a parallel between schemas and namespaces.

You may now say that in SQL Server 2000 we had the ability to create a schema with the CREATE SCHEMA statement. However, in SQL Server 2000 this does not create a schema independent of the user that creates it. It implicitly connects the user with the schema, and if you try to drop the user you run into the same problems as mentioned before.

Therefore, in SQL Server 2005, Microsoft has given us the ability to create schemas independent of the user, and by this we achieve separation of schemas and users. This gives us some considerable advantages:

• Dropping database users is greatly simplified.
  
  
• Dropping a database user does not require the renaming of objects contained by that user's schema. Thus it is no longer necessary to revise and test applications that refer explicitly to schema-contained objects after dropping the user that created them.
  
  
• Multiple users can share a single default schema for uniform name resolution.
  
  
• Shared default schemas allow developers to store shared objects in a schema created specifically for a specific application, rather than in the DBO schema.
  
  
• Permissions on schemas and schema-contained objects can be managed with a higher degree of granularity than in earlier releases.

The syntax to create a schema is like so:

CREATE SCHEMA schema_name
[AUTHORIZATION user]

where the AUTHORIZATION clause is optional. In addition to the syntax above you can also create tables, views and GRANT and DENY permissions for the particular schema when it is being created. For example, the following statement:

CREATE SCHEMA Marketing

AUTHORIZATION Bob
CREATE TABLE Mailshots (id int primary key, description varchar(max)) GRANT SELECT TO Alice
DENY SELECT TO Kent;

GO

will create the Marketing schema with Bob being the owner. Furthermore, it creates a table called Mailshots with Marketing being the schema qualifier and it allows Alice to run SELECT statements against tables in the schema but denies Kent. Schemas and users

In the CREATE SCHEMA statement above we explicitly granted and denied permissions to two users (Alice and Kent), which will be part of this schema. In SQL Server 2005 every user will have a default schema and if no default schema has been assigned to a user, she will be part of the DBO schema.

Being part of the DBO schema may initially sound a bit dangerous -- does that imply that the user then has the permissions as the dbo? No, absolutely not! Every user has to be given explicit permissions for that particular schema (or have to be be part of a role which has rights in that schema) in order to be able to do anything. The user also has to have general permissions, ie GRANT CREATE TABLE TO user-name.

The following code snippet shows an example:

--create a user called niels

CREATE USER niels FROM LOGIN niels

--by default niels is part of the DBO schema

--grant the user general table creation rights

GRANT CREATE TABLE TO niels

--change to niels and try to create a table

SETUSER `niels'

--this will fail

CREATE TABLE n(id int)

--change back to admin and grant niels table creation rights in the Marketing schema created previously

setuser
GRANT ALTER ON SCHEMA::Marketing to niels

--go back to niels

SETUSER `niels'

--try to create a table in the Marketing schema

--this will succeed

CREATE TABLE Marketing.n(id int)

Notice how the user had to schema-qualify the table name, as the user created the table in her non-default schema. To make Marketing the default schema for niels we could run following code:

ALTER USER niels
WITH DEFAULT_SCHEMA = niels

It is also worth noticing that even though niels has rights to create tables in that particular schema, he has no rights to SELECT from those tables. For that to happen we need to grant niels SELECT permissions:

GRANT SELECT ON SCHEMA::Marketing to niels

This may sound like a lot of work, and to a degree it is. In real life you'll probably find that you assign permissions to roles (as before) and then add users to the roles. Summary

Schemas gives us the ability to separate users from objects, which will make our database objects easier to administer. In addition, the introduction of schemas will also make it easier to implement a more granular security model within a database.

End of Article