Let me reiterate: Cross-database ownership-chain checking doesn't depend on the database owner but rather on the logins to which the object owners map. Some people might not realize the distinction, especially if they follow Microsoft's recommendation that the user dbo should own all objects. Regardless of who owns the objects, the difficulties described in the Microsoft article still apply. Suppose user1 in database1 owns view1, and view1 references table2 in database2, which is owned by user1 in database2. User3 in database1 has permission to select view1's data. If user3 tries to retrieve the data from view1, will SQL Server check user3's permissions on table2 in database2?
The Microsoft article now says that you need to ask instead: Do user1 in database1 and user1 in database2 map to the same login in the master database? If so, SQL Server doesn't need to check user3's permissions in database2; otherwise, SQL Server needs to verify that user3 has permission to access table2 in database2. In general, ownership chaining is good because it lets you encapsulate activities that a particular user or group of users can perform and not give users any more permissions than they need. However, when the referenced objects exist in separate databases, database chaining can compromise security.
One reason for putting objects and users into separate databases is to provide different security environments. For example, login Dan might have many permissions on one set of tables in the company's Inventory database and might even belong to the db_owner role, but in the HR database, he might be a regular user with a limited set of permissions. If the sa owns both databases, the username dbo maps to the sa login for both databases. If dbo owns a table full of sensitive data in the HR database, Dan shouldn't be able to read it because he's not privileged in the HR database. But because Dan belongs to the db_owner role in the Inventory database, he can create a view there that accesses the sensitive data in the HR database. Dan can make dbo the owner of his view so that the view in Inventory and the table in HR have the same owner (i.e., the owners map to the same login—in this case, sa). So, from the Inventory database, Dan can select from the view he created and see data in HR that he isn't supposed to see.
Changes in SP3
If you want to allow a privileged user in one database access to data in another database in which he isn't so privileged, that's your choice. But as of SQL Server 2000 Service Pack 3 (SP3), Microsoft assumes that you don't want to. Microsoft article "INF: Cross-Database Ownership Chaining Behavior Changes in SQL Server 2000 Service Pack 3" (available at http://support.microsoft.com/?kbid=810474) describes a new behavior that takes effect when you install SP3. If you want users of an object in one database to automatically be able to access dependent objects in another database that has the same owner, you must enable a new database option called db chaining in both databases.
As a shortcut, you can enable a serverwide option called cross DB ownership chaining that allows database chaining in all databases. Note that when you upgrade to SQL Server 2000 SP3 or SP3a, you can enable cross-database ownership chaining at the server level. The default is to not enable it. If you leave the option at the default, cross-database ownership chaining isn't in effect. Keep in mind that this behavior is different from the behavior in earlier releases, so applications that depend on cross-database ownership chaining probably won't work when you upgrade.
Also note that this server option isn't available through Enterprise Manager. You must use the sp_configure stored procedure as follows:
EXEC sp_configure 'Cross DB Ownership Chaining', 1
RECONFIGURE
To illustrate SP3's new behavior, log in to Query Analyzer as a system administrator, then run the code that Listing 1 shows. The code creates two new databases, database1 and database2, then creates in database2 a table containing sensitive data. Next, run the code in Listing 2, which adds a new login called user1 and grants it access to both databases, in which by default the corresponding username is user1. In database1, the code adds user1 to the db_owner database role.
Now, open a new connection by choosing File, Connect and log in as user1 (which has no password). First, verify which database you're in, then try to select the sensitive data from database2:
— Log in as user1.
SELECT db_name()
SELECT * FROM database2.dbo.sensitive_
data
You should get this error:
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object
'sensitive_data',
database 'database2', owner 'dbo'.
Prev. page
1
[2]
3
next page 
