Authorization

After SQL Server has authenticated the user and has verified that someone trying to access the SQL Server resource is who they say they are, the next step is to authorize the user to determine what resource can or cannot be accessed and what actions can or cannot be performed by that user. The authorization process essentially verifies the permissions and access rights granted to the user.

SQL Server 2005 introduces several interesting features in the authorization arena, as you will learn in this section. However, let's first look at some terms introduced in SQL Server 2005.

SQL Server 2005 Security Terminology

The following are some important terms in SQL Server 2005:

• Principal Principal is a generic term that can be used to refer to an individual Windows login or a Windows group, a SQL login, a database user, an application role, or a database role, which is used for authentication and authorization purposes in SQL Server. The sa SQL Server login and BUILTIN\Administrators Windows group are examples of principals. Each principal has a unique SID. The sys.server_principals and sys.database_principals catalog views can be used to view a list of server-level and database-level principals, respectively.

• Securable Securables are items like endpoints, databases, the Full-Text catalog, Service Broker contracts, tables, views, functions, procedures, and so on that you can secure at the server level, database level, or schema level.

• Grantor The grantor is the principal that grants a permission.

• Grantee The grantee is the principal to whom the permission is granted.

Let's now look at how SQL Server 2005 simplifies the management of permissions and incorporates support for the principle of least privileges.

Granular Permissions Control

SQL Server 2000 provided the ability to manage statement-level permission for a few statements such as CREATE DATABASE; object-level permissions such as SELECT, INSERT, DELETE, REFERENCES, or UPDATE on objects; and permissions based on fixed server-level and database-level roles. The problem with fixed roles is that they do not follow the principle of least privileges. For instance, with SQL Server 2000, a user has to be a member of a sysadmin fixed server role to run the Profiler or SQL trace. However, when you make someone part of sysadmin, that person has full control of the server and can do whatever he or she chooses to. According to the principle of least privileges, if you want grant permission to a user to perform an action, you should be able to grant the permission just for that actionnothing more, nothing less. This is what SQL Server 2005 is trying to achieve by introducing several new permission verbs and allowing you to grant permissions at multiple scopes. SQL Server 2005 provides granular permissions at the server level, database level, schema level, object level, and principal level. The SQL Server 2005 security model is hierarchical. For instance, if you grant some permission at a schema level, the principal will get that permission on all objects in the specified schema.

Examples of server-scoped permission include permissions to run a trace or create endpoints. Examples of database-scoped permissions include permissions to create tables, views, procedures, functions, a Service Broker queue, contracts and services, synonyms, schemas, XML Schema collections, and so on. Examples of schema-scoped permissions include permissions to alter any objects in a particular schema, execute any procedure or function in a particular schema, and so on. Examples of object-scoped permissions include permissions to alter a specific table, view, procedure, function, or synonym. SQL Server 2005 not only allows assigning permissions to a principal (such as a login or user), but it also defines a set of permissions on principals itself, and these permissions can in turn be granted to other principals. For instance, you can grant IMPERSONATE permission on login X to the other login Y, allowing the login Y to impersonate and run commands as login X.

Permissions in SQL Server 2005 are still managed using the familiar T-SQL constructs GRANT, DENY, and REVOKE. The GRANT statement gives permission to perform the action, DENY prevents from performing the action, and REVOKE brings to the original "unassigned" state by removing the granted or denied permission. SQL Server 2005 extends these statements and introduces several new permission verbs, such as CONTROL, IMPERSONATE, ALTER TRACE, and ALTER SERVER STATE, to provide granular control over managing permissions at multiple scopes.

Here is the syntax of a simple GRANT statement:

GRANT < permission > [ ON < scope > ]         TO < principal > < permission >::=  ALL [ PRIVILEGES ] | permission_name     [ ( column [ ,...n ] ) ] < scope >::= [ securable_class :: ] securable_name < securable_class >::= APPLICATION ROLE | ASSEMBLY | ASYMMETRIC KEY     | CERTIFICATE | CONTRACT | DATABASE | ENDPOINT | FULLTEXT CATALOG     | LOGIN | MESSAGE TYPE | OBJECT | REMOTE SERVICE BINDING | ROLE     | ROUTE | SCHEMA | SERVER | SERVICE | SYMMETRIC KEY | TYPE     | USER | XML SCHEMA COLLECTION < principal >::= Windows_login | SQL_Server_login     | SQL_Server_login_mapped_to_certificate     | SQL_Server_login_mapped_to_asymmetric_key     | Database_user | Database_role | Application_role     | Database_user_mapped_to_certificate     | Database_user_mapped_to_asymmetric_key

Before looking at examples of the GRANT statement, let's look at some of the new permission verbs introduced by SQL Server 2005:

• CONTROL With CONTROL permission, the grantee can do everything that the owner can do. For instance, if you grant CONTROL permission on a schema to someone, the grantee can do everything that the schema owner can do. However, note that you are only giving owner-like permissions to the grantee; you are not making the grantee the owner. You can use CONTROL permission to implement a subtractive model, where you start with maximum, owner-like permissions and then take away certain specific permissions.

• ALTER The ALTER permissions gives the grantee the ability to alter the properties of a particular securable and a securable contained within that scope. For instance, ALTER on a procedure gives the user permission to change the procedure text, and ALTER on a schema gives user permission to create, alter, and drop objects from the schema.

• ALTER ANY <server securable> | <database securable> This permission grants the ability to create, alter, and drop individual instances of server securables, such as logins, endpoints, and so on, or database securables, such as schemas.

• TAKE OWNERSHIP This verb is yet another great example of granular permission control in SQL Server 2005. In previous releases, you had to be a member of the sysadmin server role to change the database owner, and you had to be a member of the sysadmin server role or both the db_ddladmin and db_securityadmin database roles in order to change the object ownership. With SQL Server 2005, if you are assigned TAKE OWNERSHIP permission, you can take the ownership of the securable on which it is granted.

• IMPERSONATE <login> | <user> This is one more example of granular permission control in SQL Server 2005. Previously, only the members of the sysadmin server role or the db_owner database role could use SETUSER to impersonate another user. Now, if you are assigned IMPERSONATE permission, you can impersonate a login or user by using the EXECUTE AS and REVERT statements.

SQL Server 2000 provided the db_datareader and db_datawriter database roles, which allowed a user to read from or write to any table in the database. However, if you wanted to give EXECUTE permission on all the stored procedures and functions in the database, it was required to execute a GRANT statement for each stored procedure and function. There was no role called db_procexecutor that allowed executing any procedure in the database. The hierarchical permission model in SQL Server 2005 fixes this problem by allowing you to grant EXECUTE permission at the schema level or database level to allow users to execute any procedure within the specified schema or database, respectively. You can create your own role by using the CREATE ROLE statement and then grant EXECUTE permission to that role. Now, whoever is part of that database role will have EXECUTE permissions on the stored procedures.

Let's now look at some examples of granular permission control and the hierarchical permission model in SQL Server 2005. Let's begin by creating two users in the AdventureWorks sample database:

USE AdventureWorks; GO CREATE LOGIN Test1 WITH PASSWORD = 'PWD1'; GO CREATE USER Test1; GO CREATE LOGIN Test2 WITH PASSWORD = 'PWD2'; GO CREATE USER Test2; GO

These lines create the logins Test1 and Test2 and add them as users in the AdventureWorks sample database. Let's now grant some permissions to user Test2:

GRANT CONTROL ON OBJECT::Person.Address TO Test2; GRANT SELECT  ON SCHEMA::Person TO Test2; GRANT EXECUTE ON DATABASE::AdventureWorks TO Test2; USE master; GRANT ALTER TRACE TO Test2;

These GRANT statements allow the Test2 user to have full control on the Person.Address table, much like the owner of the table; query any object in the Person schema; and execute any procedure or function in the AdventureWorks database. The ALTER TRACE permission allows Test2 to run Profiler to trace SQL Server. Because ALTER TRACE is a server-scoped permission, the statement has to be run in the master database.

The following statement illustrates principal-scoped permission and the new IMPERSONATE permission verb to allow user Test1 to impersonate user Test2:

USE AdventureWorks; GRANT IMPERSONATE ON USER::Test2 TO Test1;

Tip

You can use the sys.server_permissions and sys.database_permissions catalog views to view server-level and database-level permission details.

You can start a new query window, connect as Test1, and run following statements:

USE AdventureWorks; SELECT * FROM Person.Contact; GO ALTER TABLE Person.Address ADD col1 int NULL; GO ALTER TABLE Person.Address DROP COLUMN col1; GO EXEC dbo.uspGetEmployeeManagers 1; GO

You should notice that all the four statements fail because user Test1 does not have permission to select from the Person.Contact table or change the Person.Address table or execute the stored procedure.

You can run the following line to impersonate the Test2 user and then run the preceding four statements. This time, the batch succeeds without any error messages because Test2 user has the required permissions:

EXECUTE AS USER='Test2'; GO SELECT USER_NAME() GO --Run the earlier batch again

You can run the REVERT statement to switch back to the Test1 user execution context. Close this query window.

Next, you should start the Profiler tool and connect as user Test2. You should then be able to run SQL trace even though Test2 is not part of the sysadmin server role but has ALTER TRACE server-level permission. After you have verified that you can run Profiler trace by connecting using the Test2 login, close the Profiler and return to original query window in Management Studio, where you should execute the following lines to clean up:

USE AdventureWorks; GO DROP USER Test1; GO DROP USER Test2; GO DROP LOGIN Test1; GO DROP LOGIN Test2; GO

Tip

You can use the sys.fn_builtin_permissions function to see SQL Server's built-in permission hierarchy. You can pass DEFAULT or a securable classname, such as SERVER, DATABASE, SCHEMA, OBJECT, and so on, as a parameter. This is a table-valued function, so you can use it in a SELECT statement as shown here:

SELECT * FROM sys.fn_builtin_permissions(DEFAULT);

Within the SQL Server 2005 permissions hierarchy, granting a particular permission may convey the rights of other permissions by implication. High-level permissions may be described as "covering" the more granular, low-level permissions that they "imply." SQL Server 2005 Books Online contains a user-defined function called ImplyingPermissions that uses the sys.fn_builtin_permissions function to obtain a list of list of permissions that include the specified permission by implication. This function accepts the name of a class of securable (such as schema or object) and the name of a permission (such as alter or execute) and returns a list of permissions that include the specified permission by implication. You can search for the word "ImplyingPermissions" in Books Online for more details on this.

Module Execution Context

As described in the previous section, the SQL Server 2005 security model allows granting permissions to users and logins and also granting permissions on users and logins (principals in general). One such principal-level permission is IMPERSONATE, which allows a user or login to change the execution context and perform actions as an impersonated user or login. As illustrated earlier, once the IMPERSONATE permission is granted, the EXECUTE AS statement can be used to switch the execution context, and the REVERT statement can be used to revert to the previous execution context.

SQL Server 2005 introduces a concept called module execution context that allows you to specify the execution context under which to execute a module, such as a stored procedure, a function (except an inline table-valued function), or a trigger. You can specify the EXECUTE AS clause with DDL statements such as CREATE PROCEDURE, CREATE FUNCTION, and CREATE TRIGGER to implicitly define the execution context of these user-defined modules. You can just grant the EXECUTE permission on the module to the users, and when the module is actually executed, the database engine will use the account specified with EXECUTE AS to validate permissions on objects referenced in the module. This way, you don't have to separately give permissions on the referenced objects to users of the module.

This might seem somewhat similar to the concept of ownership chaining that was available in previous releases. As a matter of fact, module execution context is designed to fix some of the limitations of ownership chaining.

Ownership Chaining and Its Limitations

The notion of ownership chaining simplifies permissions management and also yields small performance gains. The term chain refers to a virtual link that is established when an object, such as a stored procedure or a function, accesses other objects, such as tables and views.

For instance, if UserX executes the procedure UserY.Proc1, SQL Server first checks whether UserX has EXECUTE permission on UserY.Proc1. Once that is verified, if UserY.Proc1 queries the table UserY.Table1, SQL Server does not check whether UserX has permissions on UserY.Table1 because the procedure owner and object owner are the same (that is, UserY), forming an ownership chain. This way, you don't have to grant permissions on referenced objectsjust the EXECUTE permission on stored procedures. By avoiding the extra permission check, SQL Server provides a small performance gain. However, if the procedure UserY.Proc1 queries the table UserZ.Table2, SQL Server checks whether the caller, UserX, has permissions on UserZ.Table2 because the referenced table owner (that is, UserZ) is different than the procedure owner (that is, UserY), breaking the ownership chain.

As you can see, ownership chaining is a very useful concept. However, it suffers from three limitations:

• The ownership chain works only for DML statements (SELECT, INSERT, UPDATE, and DELETE) and does not work for DDL statements. For instance, in the preceding example, if the stored procedure UserY.Proc1 alters UserY.Table1, even though the executing object owner and referenced object owner are the same, SQL Server still checks permissions to ensure that UserX has sufficient permissions to alter the table.

• For security reasons, SQL Server explicitly blocks or disables ownership chaining for dynamic SQL.

• Ownership chaining relies on the fact that that the owners in the chain are the same. Ownership chaining is not designed to let you run a module under a different user context.

All these limitations are addressed by the module execution context feature introduced in SQL Server 2005. If you specify the EXECUTE AS clause with the module definition, SQL Server runs the entire module under the security context of the specified account, regardless of whether the ownership boundaries are crossed, the module contains DDL statements, or the module uses dynamic SQL. The concept of ownership chaining and module execution context is illustrated in Figure 7.1.

Figure 7.1. SQL Server 2005 introduces the EXECUTE AS clause, which you can use to implicitly specify the security context to be used while running a user-defined module.

Let's now look at module execution context features and the EXECUTE AS clause in more detail.

The EXECUTE AS Clause

The WITH EXECUTE AS clause can be used to explicitly define the execution context of the user-defined modules, including stored procedure, functions, DML triggers, DDL triggers, and Service Broker queues. The EXECUTE AS clause can be specified with the CREATE or ALTER DDL statement for user-defined modules. You can specify one of the following four execution contexts for the module:

EXECUTE AS {CALLER | 'UserName' | SELF | OWNER}

The default is CALLER (except in Service Broker queues, where SELF is the default), which provides the same execution context behavior as in previous releasethat is, the permission check is skipped if the referenced object owner and module owner are the same, forming the ownership chain. If the referenced object owner and the module owner are different, breaking the ownership chain, SQL Server ensures that the caller has the permissions on the referenced object. By specifying an execution context other than CALLER, you are telling SQL Server that you cannot rely on ownership chaining and that you would like to specify an account under which it should run the module.

In order to specify <UserName> with EXECUTE AS, you must have the IMPERSONATE permission on that user. The statements in the module are then executed in the security context of the specified account. For server-scoped DDL triggers, you have to specify a login name.

The SELF execution context indicates the user creating or altering the module. SELF is the default for Service Broker queues. SELF is essentially a shortcut to <UserName>, where SQL Server puts the name of the user creating or altering the procedure.

The OWNER execution context tells SQL Server 2005 to run the module under the security context of the current module owner at the time of execution. While creating the module, if you are specifying a different owner, you must have the IMPERSONATE permission on the module owner. The specified owner must be a singleton account, and it cannot be a role or a group.

Note

execute_as_principal_id in the sys.sql_modules system catalog view can be used to find out about the module execution context. If this column contains NULL, it indicates the CALLER execution context, -2 indicates OWNER, and any other number indicates the principal ID of the user specified as SELF or <principal>.

Let's now look at EXECUTE AS in action. In the following example, the user Sean is a DBA who would like to allow the user Erin to truncate a table but not do anything else with that table. Because trUNCATE is not a grantable permission, Sean creates a stored procedure and specifies the EXECUTE AS SELF clause and grants the EXECUTE permission to user Erin. This way, the DBA creates his own new "permissions bucket."

You can begin working with this example by creating the users Sean and Erin:

USE AdventureWorks; GO CREATE LOGIN Sean WITH PASSWORD = '1234'; GO CREATE LOGIN Erin WITH PASSWORD = 'pqrs'; GO CREATE USER Sean; CREATE USER Erin; EXEC sp_addrolemember 'DB_OWNER', 'Sean'; GO

Next, you need to launch a new query window, connect as login Sean with password 1234, and run the following statements to create a table and the stored procedure to truncate the table:

USE AdventureWorks; GO CREATE TABLE dbo.tblTestData  (col1 INT IDENTITY(1,1) NOT NULL PRIMARY KEY,   col2 VARCHAR(10) DEFAULT 'Test'); GO INSERT INTO dbo.tblTestData DEFAULT VALUES; INSERT INTO dbo.tblTestData DEFAULT VALUES; INSERT INTO dbo.tblTestData DEFAULT VALUES; INSERT INTO dbo.tblTestData DEFAULT VALUES; GO SELECT * FROM dbo.tblTestData; GO CREATE PROCEDURE dbo.usp_TruncateTestData WITH EXECUTE AS SELF AS    SELECT USER_NAME();    TRUNCATE TABLE dbo.tblTestData; GO GRANT EXECUTE ON dbo.usp_TruncateTestData TO Erin; GO

Then you need to launch one more query editor window. This time connect as user Erin with password pqrs and run the following script:

USE AdventureWorks; GO SELECT * FROM sys.all_objects WHERE type = 'U'; GO SELECT * FROM dbo.tblTestData; GO EXEC dbo.usp_TruncateTestData; GO

Because the user Erin does not have permission on any user table, the query on the sys.all_objects catalog view returns an empty set, even though there are many user tables in the database. This is because of the new metadata security feature introduced in SQL Server 2005, which is discussed later in this chapter.

Let's say that somehow user Erin found out that there is a table called tblTestData. If she tries to run the SELECT statement on this table, the operation fails because Erin does not have permission to query the table. However, when Erin runs the stored procedure, SQL Server runs the procedure under the security context of user Sean, as shown by the SELECT USER_NAME() statement inside the procedure. And because user Sean has full control on the table, the truncate statement inside the usp_TruncateTestData stored procedure succeeds.

Next, you need to close the query window connections made using the logins Sean and Erin and return to the original query window, where you can run the following statements to clean up:

DROP TABLE dbo.tblTestData; DROP PROCEDURE dbo.usp_TruncateTestData; DROP USER Sean; DROP USER Erin; DROP LOGIN Sean ; DROP LOGIN Erin ; GO

If you comment the WITH EXECUTE AS clause while creating the stored procedure, and if you follow the preceding demo steps again, you notice that when Erin executes the stored procedure, it fails because it is executing under her security context, and she does not have any permission on the table. If the procedure had the SELECT statement instead of the trUNCATE statement, the stored procedure would work because of ownership chaining (the procedure owner and table owner are the same). However, ownership chaining works only for SELECT, INSERT, UPDATE, and DELETE statements, and not for TRUNCATE or any DDL statements, so this procedure fails if EXECUTE AS is commented. This example illustrates that EXECUTE AS works, regardless of the type of statements in a module.

UserSchema Separation

In SQL Server 2000, a fully qualified object name consists of four partsserver name, database name, owner name, and object nameand can be specified by using the following format:

[[[server.][database].][owner_name].]object_name

The objects can have the same name, as long as they have a different owner. In other words, you can have two tables named tblTest, as long as they are owned by different users. In that case, the applications qualify the object as User1.tblTest or User2.tblTest, depending on which table they wants to access. As you can see, here the username is used to avoid duplicates and to avoid name collisions. As per the ANSI SQL-92 standard, this is the job of a schema. SQL Server 2000 treated the schema and user the same. In other words, the schema and user were tightly coupled; they were one and the same. As per the ANSI SQL-92 standard, the purpose of schemas is to act like a namespace to club related objects and to avoid name collisions. With SQL Server 2000, the usernames were used for this purpose, and there was no true support for schemas.

Although this approach worked, there are a few problems. Let's say UserX created a bunch of tables, stored procedure, functions, views, and so on. And the application accessed the objects as UserX.object_name. Now, let's say for some reason that you wanted the drop UserX after the application went into production. You cannot drop a user unless all the objects the user owns are dropped or the ownership is assigned to a different user. Dropping the objects in almost all cases is not possible. And changing the owner requires that the application change. After the ownership is assigned to UserY, for example, the application has to access the objects as UserY.object_name instead of UserX.object_name.

Application developers often use dbo as the object owner to avoid the problem just described and also to avoid any name resolution issues. If the object is not fully qualified, SQL Server first tries to find an object with the specified name that is owned by the currently logged-in user. If not found, SQL Server tries to find dbo.object_name. For example, if UserX is trying to access table tblTest, and the table name is not fully qualified with the owner name, SQL Server first tries to find UserX.tblTest, and if it is not found, SQL Server tries to find a table named dbo.tblTest. This is the reason it is recommended that you fully qualify objects to avoid this extra name resolution check. Using dbo to group objects works fine until an application needs to create the objects and even the new objects have to be under the dbo ownership to aid name resolution. Just the CREATE TABLE permission is not sufficient to create a table in the dbo namespace. You have to be a member of either the sysadmin fixed server role or the db_dbowner or db_ddladmin fixed database role to create an object with dbo as the owner.

SQL Server 2005 decouples users (or principals in general) from schemas by changing the four-part naming convention to this:

[[[server.][database].][schema_name].]object_name

As you can see, with SQL Server 2005, the schema is the third part of the fully qualified object name. Schemas in SQL Server 2005 provide the notion of namespaces, allowing you to club the related objects together under a single name. In SQL Server 2000, objects were owned by users, whereas in SQL Server 2005, objects are contained in a schema that is owned by a user. Because a user does not own the objects directly, a user can be dropped without the objects needing to be dropped. By just assigning the schema owner to some other user, you can drop the user. Changing the schema owner does not require changing the application because the application continues to access the object as schema_name.object_name, regardless of who the schema owner is. This is illustrated in Figure 7.2.

Figure 7.2. SQL Server 2005 decouples users from the schema to simplify the security management and for ANSI SQL-92 compliance.

Schema can be created by using the CREATE SCHEMA DDL statement. The sys.schemas catalog view can be used to view a list of schemas in the current database.

Note

The CREATE SCHEMA T-SQL statement was available in SQL Server 2000. However, it did not actually create a schema as SQL Server 2005 does. In SQL Server 2000, using CREATE SCHEMA was merely a way to create objects and grant permissions in a single statement and to aid system administrators in managing object dependencies.

Here is a simple example of how to create a schema and create objects in that schema:

USE AdventureWorks; GO CREATE SCHEMA Inventory; GO CREATE TABLE Inventory.tblReportData    (col1 INT PRIMARY KEY NOT NULL IDENTITY(1,1),     col2 XML DEFAULT '<Report />'); GO INSERT INTO Inventory.tblReportData DEFAULT VALUES; SELECT * FROM Inventory.tblReportData; GO SELECT name AS SchemaOwner FROM sys.database_principals    WHERE principal_id =    (SELECT principal_id FROM sys.schemas        WHERE name = 'Inventory'); GO SELECT * FROM sys.all_objects WHERE schema_id =    (SELECT schema_id FROM sys.schemas        WHERE name = 'Inventory'); DROP TABLE Inventory.tblReportData; GO DROP SCHEMA Inventory; GO

This script creates a schema named Inventory and then creates a table in that schema. Note how this new table is accessed by INSERT and SELECT statements, using the schema_name.table_name convention, rather than user_name.table_name. This script uses the sys.schemas, sys.database_principals, and sys.all_objects catalog views to find out about the schema owner and the objects in the schema. The schema owner depends on the account you are logged in as while running the script. To drop the user, all you have to do is assign the schema ownership to some other user, and the application can continue to access the object as Inventory.tblReportData.

Multiple objects can still have the same name, as long as they are in different schemas. Because objects are not owned by users anymore, you might ask how name resolution works. How does SQL Server 2005 determine which object to access when a fully qualified name is not provided? The answer to this question is a new concept called default schemas.

Default Schemas

When you create or alter a user by using a CREATE USER or ALTER USER DDL statement, SQL Server 2005 gives you an option to provide a default schema for that user. You can use the WITH DEFAULT_SCHEMA = schema_name construct to specify the default schema for that user. If you do not make use of this construct while creating the user, by default SQL Server 2005 makes dbo as the user's default schema. The notion of default schemas is used for name resolution when an object is not fully qualified. SQL Server looks for the default schema for the user trying to access the object, and if the object is not found in user's default schema, SQL Server searches for the object in the dbo schema.

Here's an example of a default schema:

USE AdventureWorks; GO CREATE LOGIN demoLogin WITH PASSWORD = '1234'; CREATE USER demoLogin WITH DEFAULT_SCHEMA = Sales; GRANT SELECT ON OBJECT::Sales.Store TO demoLogin; GRANT EXECUTE ON XML SCHEMA COLLECTION::Sales.StoreSurveySchemaCollection     TO demoLogin; GO EXECUTE AS USER = 'demoLogin'; GO SELECT * FROM Store; GO SELECT * FROM Sales.Store; GO REVERT; GO ALTER USER demoLogin WITH DEFAULT_SCHEMA = Production; GO EXECUTE AS USER = 'demoLogin'; GO SELECT * FROM Store; GO SELECT * FROM Sales.Store; GO REVERT; GO DROP USER demoLogin; DROP LOGIN demoLogin; GO

This script creates a login named demoLogin and a user with the same name in the AdventureWorks sample database. While creating the user, a schema named Sales is made as the default schema for the user. The script then grants permissions so that this user can run SELECT queries on the Sales.Store table. The security execution context is then changed by using an EXECUTE AS statement to run the next SELECT statement under the demoLogin user's context. Both the SELECT statements succeed because the user's default schema is Sales. When an object is not fully qualified, SQL Server looks for an object in user's default schema. If it is not found, SQL Server looks in the dbo schema namespace. In this case, it finds the Store table in the user's default schema. The script then changes the user's default schema to Production. The same SELECT then fails because it does not find the Store table either in the Production schema or in the dbo namespace.

Note

Notice that the sp_addlogin and sp_adduser system stored procedures are not used in the preceding script. They are deprecated in this release, and their use is strongly discouraged. The new T-SQL statements CREATE LOGIN and CREATE USER should be used instead. When you run CREATE USER, you have an option to provide the user's default schema, and if it is not provided, it defaults to dbo. However, if you create a user by using the sp_adduser stored procedure, SQL Server 2005 creates a schema with the same name as the username. This schema is then set as the default schema for this user, and the user is marked as the owner of this schema. The sp_dropuser stored procedure is updated to remove the schema created by sp_adduser. The sp_grantdbaccess and sp_revokedbaccess stored procedures are also updated to create and drop the schema with the same name as the username. Like sp_adduser and sp_dropuser, the sp_grantdbaccess and sp_revokedbaccess stored procedures are deprecated, and you should use the CREATE USER and DROP USER statements instead.

The notion of default schemas allows applications to not fully qualify objects but still guarantees to find the objects. As described earlier, in SQL Server 2000 this was achieved by having dbo as the owner. However, as alluded to earlier, creating objects to have dbo as the owner required high privileges, such as being part of the sysadmin, db_dbowner, or db_ddladmin roles. The default schema notion also fixes this problem. You can create a schema and have that as the default schema for all your users. Users can now create objects in this schema without requiring high privileges, and they can access the objects without fully qualifying the objects.

Note

If you look at rows in the sys.schemas catalog view, you see an entry for every fixed database role. For instance, there are schemas named db_owner, db_ddladmin, and so on. SQL Server 2005 creates these schemas in every new database to provide backward compatibility with SQL Server 2000. In previous releases, you could create an object in the form fixed_db_role.object_name. Although not very common, some developers might have created objects owned by fixed database roles. To provide backward compatibility, SQL Server 2005 creates a schema name for every fixed database role. However, there is no requirement or system reason to use these schemas.

A SQL Server 2005 database may consist of many schemas. Each schema has an owner. A user can own multiple schemas; however, there is always a single owner per schema. Each user has a single default schema that aids SQL Server in name resolution. In order to create objects in a schema, the user must have the CREATE permission and also ALTER or CONTROL permission on the schema.

Secure Metadata

SQL Server 2005 disallows direct access to system tables and exposes persistent metadata in the form of catalog views. SQL Server 2005 implements a security layer on top of the catalog views to restrict access to metadata and to make it visible only on a need-to-know basis. Row-level filtering on a catalog view ensures that if the user is not the owner or does not have permission to view the metadata, the catalog view row is not returned.

SQL Server 2005 defines a new permission, VIEW DEFINITION, which can be granted to a user to allow access to the metadata. Like other permissions, the VIEW DEFINITION permission can be granted at multiple scopes such as at database, schema, or object level.

Note

The database metadata (that is, the content of sys.databases catalog view) is never hidden from any database user. The system administrator has access to the entire server metadata, and the database owner has access to the entire database metadata.

Here is an example of catalog security in action:

USE AdventureWorks; GO CREATE LOGIN login1 WITH PASSWORD = 'WjkKWjkF'; GO CREATE USER user1 FOR LOGIN login1; GO EXECUTE AS USER = 'user1'; GO EXEC sp_helptext 'dbo.uspGetBillOfMaterials'; GO SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.uspGetBillOfMaterials')); GO SELECT * FROM sys.objects WHERE type = 'U'; GO SELECT * FROM INFORMATION_SCHEMA.TABLES; GO REVERT; GO GRANT VIEW DEFINITION ON OBJECT::dbo.uspGetBillOfMaterials TO user1; GRANT VIEW DEFINITION ON SCHEMA::HumanResources TO user1; GO EXECUTE AS USER = 'user1'; GO EXEC sp_helptext 'dbo.uspGetBillOfMaterials'; GO SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.uspGetBillOfMaterials')); GO SELECT * FROM sys.objects WHERE type = 'U'; GO SELECT * FROM INFORMATION_SCHEMA.TABLES; GO REVERT; GO DROP USER user1; DROP LOGIN login1; GO

This script creates a user who initially does not have any permission. When this user tries to see the stored procedure text by using sp_helptext, SQL Server 2005 returns an error, and OBJECT_DEFINITION returns NULL. When this user tries to obtain a list of user tables by using the sys.objects catalog view or by using the INFORMATION_SCHEMA view, SQL Server 2005 returns an empty result set because the user does not have permission on any of the tables and also does not have VIEW_DEFINITION permission. When the VIEW_DEFINITION permission is granted, the user can see the stored procedure text and can see all the tables in the schema on which the VIEW_DEFINITION permission is granted. The script uses EXECUTE AS and REVERT statements to change the security execution context.