Security Enhancements in SQL Server 2005: Schema

Default Schema

Although the best practice is to access database objects by specifying the fully qualified name, most of the time users access objects by providing simple select statements. In addition, most of the time users may not access several schemas. For example, accounts people will use the accounts schema while human resources people will use the HR schema. Therefore, it would be useless to specify a schema all the time. To avoid having to do this, there is an attribute called default schema for each user.

As the screenshot above indicates, HumanResources is the default schema for user dinesh. This means that when dinesh accesses database objects in the above schema, he does not have to specify the schema explicitly. However, for better performances and for good practice it is advised to specify the schema explicitly.

The following T-SQL script will assign HumanResources as the default schema for user dinesh.

ALTER USER dinesh WITH DEFAULT_SCHEMA=HumanResources

Sys Schema

The schema that you'll use most frequently is the sys schema. All the system tables, views, and stored procedures belong to this schema. To access system objects, a particular user should have permission on sys schema. For example, just as you can get users from the sysusers system table in SQL Server, you can get schema details from the sys.schema catalog view.

Upgrading from SQL Server 2000

So what will happen when you upgrade databases from SQL Server 2000 to SQL Server 2005?

Let us assume that you have upgraded a database by means of a backup and restore. You have a SQL Server 2000 database with user User2000 and this user owns a few objects. After the upgrade, you'll see a schema named User2000 whose owner is User2000. All the objects that belonged to the user have been put into the schema User2000. Because of this, you do not have to change your previous codes if you have used fully qualified texts.

Another important factor you'll need to remember is that you will not be able to delete a user after upgrading to SQL Server 2005 because that particular user is now the owner of a schema. To delete the user, you will first have to delete that user's schema or change its owner.

Conclusion

Schema is the new security enhancement in SQL Server 2005. For developers and administrators, it also reduces the hassles associated with deleting users.