Avoiding Cross Database Ownership Chaining

Cross database ownership chaining is bad. Very bad. How bad? Despite being included in SQL Server 2005, this feature is disabled by default. SQL Server 2005 Books Online goes on to say “Setting cross db ownership chaining to 1 is not recommended unless all of the databases hosted by the instance of SQL Server must participate in cross-database ownership chaining and you are aware of the security implications of this setting. For more information, see Ownership Chains.” Why would you want to use this? Well, you might want to write a stored procedure or function that pulls data from two databases in the same query joining between the two of them. At my current client, I have two databases on the server — a data mart and a web reporting database. There are materialized report tables in the web reporting database. Some of these tables are only used to provide filters for pulling data from the data mart. Access to the database is very limited — the web user can only execute stored procedures. This afternoon, after struggling with cross database permission chaining for a day and a half, I posted on sqlservercentral.com’s forums. I was ultimately helped out by a user who suggested using the database option TRUSTWORTHY. I had never heard of the TRUSTWORTHY option, so I had to do some digging to get a better understanding before I suggested it as an option to my client. What does the TRUSTWORTHY option do?

“When ON, database modules (for example, user-defined functions or stored procedures) that use an impersonation context can access resources outside the database. When OFF is specified, in an impersonation context cannot access resources outside the database. TRUSTWORTHY is set to OFF whenever the database is attached.”

TRUSTWORTHY allows databases to use impersonation to access resources in a different database. Here’s an example:

• There are two databases on the server: data_mart and web_reports
• There is a server level login: test_user
• The test_user login is mapped to the test_user user on both data_mart and web_reports

At this point, TRUSTWORTHY is set to OFF. If I try to execute a stored procedure in the web_reports database, I will get an error message that looks like: The server principal “test_user” is not able to access the database “data_mart” under the current security context. The only way to fix this is to turn on the TRUSTWORTHY option on both databases: ALTER DATABASE web_reports SET TRUSTWORTHY ON; GO ALTER DATABASE data_mart SET TRUSTWORTHY ON; GO After executing the ALTER DATABASE code, stored procedures that cross databases will execute properly. It’s important to note that, in this situation, the stored procedures will either need to be owned by test_user or else they will need to be set to EXECUTE AS ‘test_user’.