SQL Server 2005 Schemas -- separate users from objects

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

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 atable 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, ifwe for some reason wanted to delete Bob from the database (DROP USER BOB), we could not dothat 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 theobjects 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 betweenschemas and namespaces.

You may now say that in SQL Server 2000 we had the ability to create a schema with theCREATE SCHEMA statement. However, in SQL Server 2000 this does not create a schemaindependent 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 schemasindependent of the user, and by this we achieve separation of schemas and users. Thisgives us some considerable advantages:

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 canalso create tables, views and GRANT and DENY permissions for the particular schema when itis being created. For example, the following statement:

CREATE SCHEMA Marketing

GO

will create the Marketing schema with Bob being the owner. Furthermore, it creates atable called Mailshots with Marketing being the schema qualifier and it allows Alice torun 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 totwo users (Alice and Kent), which will be part of this schema. In SQL Server 2005 everyuser will have a default schema and if no default schema has been assigned to a user, shewill be part of the DBO schema.

Being part of the DBO schema may initially sound a bit dangerous -- does that implythat the user then has the permissions as the dbo? No, absolutely not! Every user has tobe given explicit permissions for that particular schema (or have to be be part of a rolewhich has rights in that schema) in order to be able to do anything. The user also has tohave 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 Marketingschema 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 tablein her non-default schema. To make Marketing the default schema for niels we could runfollowing code:

ALTER USER niels
WITH DEFAULT_SCHEMA = niels

It is also worth noticing that even though niels has rights to create tables in thatparticular schema, he has no rights to SELECT from those tables. For that to happen weneed 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 probablyfind 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 ourdatabase objects easier to administer. In addition, the introduction of schemas will alsomake it easier to implement a more granular security model within a database.