Backward Compatibility
SQL Server 2000 doesn't separate usernames and schema—it doesn't even contain the concept of schema. Thus, SQL Server 2000 stamps any object created by a user with the username in place of a schema. In SQL Server 2005, to maintain backward compatibility, Microsoft rewrote the sp_grantdbaccess and sp_adduser procedures to use the new Data Definition Language (DDL) statements I discuss in the sidebar "New DDL."
In SQL Server 2005, sp_grantdbaccess creates a user and a schema that has the same name and ID as the user. This schema is the user's default schema, and the user is the schema owner. For example, if someone executes the procedure
EXEC sp_grantdbaccess Susan, sue, db_datareader
SQL Server 2005 translates it internally into the new DDL, as Listing 1 shows. In addition, SQL Server 2005 internally rewrites sp_revokedbaccess sue as
DROP SCHEMA sue
DROP USER sue
Note that SQL Server 2005 drops the schema only if it has the same name and ID as the user.
Remember that if you run CREATE USER instead of sp_grantdbaccess and you don't specify a default schema, the default schema is dbo. SQL Server 2005 won't create a new schema with the same name as the user.
Get Ready Now
Even if you don't plan to upgrade immediately to SQL Server 2005 when it's released, I recommend you start thinking of users and schemas as separate things. In SQL Server 2000, schemas always have the same name as users, but you can keep them straight by remembering that you grant permissions to users, whereas schemas contain (and qualify) objects.
The separation of users and schemas will ease administration in several ways. The biggest benefit is that managing users, particularly dropping users, will be much easier. In addition, you can set multiple users as owners of a schema by using secondary principals (e.g., groups, roles). And multiple users can have the same default schema for common name resolution.
The ability to specify a user as the executor for a particular module gives you more control over object access and is a big improvement over the cross-database ownership chaining control that Microsoft introduced in SQL Server 2000 Service Pack 3 (SP3). Cross-database ownership is an all-or-nothing solution: Ownership chaining applies across databases, or it doesn't. With the additional control in SQL Server 2005, a module developer can decide which modules users can execute and which ones they can't.
These enhancements are only the tip of the Yukon security-feature iceberg. Stay tuned for more new security features, and as we get closer to SQL Server 2005's release, I'll dig into details about using the features.
End of Article
Prev. page
1
2
[3]
next page -->
