Systems Engineering and RDBMS

Archive for May 19th, 2007

User Schema Separation in SQL Server 2005

Posted by decipherinfosys on May 19, 2007

One important change that was introduced in SQL Server 2005 was the user and schema separation. Prior to SQL Server 2005, the user in the database was also the name of the schema and the user owned the schema. So, the built in user dbo was a schema as well which contained all the objects. Example:

User Owner of Schema Object Type
Mary    Mary                  T1        Table
Frank   Frank                 T1        Table

Schemas just serve the purpose of containers which contain the different types of objects – tables, views, procedures, functions etc. and users in the database can be granted access to a schema. A user can now own more than one schema and the ownership can be easily transferred from one user to another. So, previously an object used to be referenced as:


and now it is referenced as:


One key advantage of this is that the users can be easily removed without dropping the objects in that schema. So, in the above example, “Decipher.DecipherDEMODB.Frank.T1” would mean that the object T1 is owned by the user “Frank”. If the DBA then wants to delete the user Frank, he/she must first delete the object or change it’s owner (in SQL 2000). In SQL 2005, one can just re-name it to be: “Decipher.DecipherDEMODB.Mary.T1”

User Owner of Schema Object Type
Mary    Frank                 T1        Table
Mary    Mary                  T1        Table

These schemas exist independent of the users that create them and in addition, multiple database users can share the same schema. You can grant permission on the schema and deny on individual table.

As far as the default schemas go, in prior versions, if a fully qualified name was not specified for an object, the engine used to look-up under the schema owned by the database user as well as under the default schema: “dbo”. In SQL 2005, each user can be assigned a default schema (this can be changed as well) and if no default schema is specified, the engine assumes that “dbo” is the default schema.

Posted in SQL Server | Leave a Comment »