After a user is successfully authenticated against SQL Server, the authorization process starts to determine which rights the user has on the database. In this area, SQL Server 2005 has two new features:
- Separation of users and schemas
- Execution context
|Figure 3. User and Schema Relationship in SQL Server 2000|
Separation of Users and Schemas
A schema is a container where you can logically group database objects (tables, stored procedures, views, etc.). It is the same as a namespace in the .NET Framework base class library. Because a schema in SQL Server also can have an owner, you can dictate that each object in a given schema have the same owner. SQL Server 2000 doesn't have a good implementation of schemas: the name of the schema is the same as the name of the user. Therefore, a direct relationship exists between the schema and the owner of the database objects (see Figure 3
The big problem in SQL Server 2000 is that objects in a given schema must be addressed with a fully qualified name (schema.objectname), but when you drop a user from the database, the fully qualified name of an object changes. Therefore, the changes you made in the database unfortunately also occurred in the queries on the client side, and such changes were sometimes costly. With SQL Server 2005, the schema is a standalone native object in the database with its own name and owner. Such a schema has no relationship to the user (see Figure 4).
|Figure 4. User and Schema Separation in SQL Server 2005|
Now you can delete and add new users to the database without making changes in the software (neither on the client side nor on the database side). Furthermore, you can assign rights to a schema to control access to the objects in that particular schema. Listing 2 shows schema usage in SQL Server 2005. This code is also available as a download in the file Schemas.sql.
-- Create database logins
CREATE LOGIN Paul WITH PASSWORD='p@ssw0rd1'
CREATE LOGIN Mary WITH PASSWORD='p@ssw0rd1'
CREATE USER Paul FOR LOGIN Paul
CREATE USER Mary FOR LOGIN Mary
-- Create a new schema
CREATE SCHEMA SalesData
-- Create a new table in the schema "SalesData"
CREATE TABLE SalesData.SalesPromotion
GRANT ALL ON SalesData TO Paul
ALTER USER Paul WITH DEFAULT SCHEMA = SalesData
GRANT SELECT ON SCHEMA::SalesData TO MARY
Another new feature in SQL Server 2005 allows you to change the execution context of a stored procedure, a user-defined function, or a trigger. You can control under which user these database objects execute by changing the execution context with the EXECUTE AS statement, which supports the following options:
- EXECUTE AS CALLER
- EXECUTE AS USER='user name'
- EXECUTE AS SELF
- EXECUTE AS OWNER
EXECUTE AS CALLER is the default option, through which a stored procedure is executed with the identity of the calling user. With EXECUTE AS USER, you specify the user under which the stored procedure should run. When you use EXECUTE AS SELF, the stored procedure executes with the identity of the creator. Finally, you can use EXECUTE AS OWNER to execute the stored procedure with the identity of the object owner.