Options for cross database access within SQL Server

By: Diana Moldovan   |   Updated: 2011-11-30   |   Comments (2)   |   Related: > Security

Problem

I need to create a stored procedure that queries data that are not in the current database. What are my options?

Solution

In a simple environment, where both databases are owned by the same account or you are a member of the sysadmin role, there is no problem regarding the cross database access.

Let's talk about more complex environments, where work is done using non administrative accounts. Here are the options you have:

1. Enabling the cross database ownership chaining.

For example, if the stored procedure from the first database and the table from the second database have the same owner and the "db_chaining" option is "true" for both databases there is a ownership chain between the two objects. However before using this feature be aware of the risks.

2. Setting the first database as TRUSTWORTHY and granting its owner AUTHENTICATE in the second database.

To demonstrate this method, I'll use two databases (db1 and db3) owned by non administrative logins (user1 and user3).

USE master;
--logins
CREATE LOGIN user1 WITH PASSWORD = 'P@ssword_usr1';
GO
CREATE LOGIN user3 WITH PASSWORD = 'P@ssword_usr3';
GO
--databases
IF EXISTS (SELECT * FROM sys.databases WHERE [name] = 'db1')
DROP DATABASE db1;
GO
CREATE DATABASE db1;
GO
IF EXISTS (SELECT * FROM sys.databases WHERE [name] = 'db3')
DROP DATABASE db3;
GO
CREATE DATABASE db3
GO
--changing the owners of the dbs
ALTER AUTHORIZATION ON DATABASE::db1 TO user1;
GO
ALTER AUTHORIZATION ON DATABASE::db3 TO user3;
GO

In db3 I'll create a demo table. I'll try to select from this table using a stored procedure residing in db1.

USE db3;
CREATE TABLE Table3(RecID tinyint);
GO
USE db1;
GO
CREATE PROCEDURE proc1
   WITH EXECUTE AS OWNER
AS
SELECT RecID FROM db3.dbo.Table3;
GO

The proc1 stored procedure will be executed by the StoredProcExecutor1 user.

USE master;
CREATE LOGIN StoredProcExecutor1 WITH PASSWORD = 'P@ssword_sp1';
GO
USE db1;
CREATE USER StoredProcExecutor1 FOR LOGIN StoredProcExecutor1;
GO
GRANT EXEC ON proc1 TO StoredProcExecutor1;
GO  

Until now I've been logged in as a sysadmin account. Let's execute the stored procedure as the StoredProcExecutor1 user.

USE db1;
EXECUTE AS USER = 'StoredProcExecutor1';
EXEC proc1;
SELECT * FROM sys.user_token;
SELECT * FROM sys.login_token;
GO
REVERT;

Unfortunately this time the only result is an error message:

Msg 916, Level 14, State 1, Procedure proc1, Line 4
The server principal "user1" is not able to access the database "db3" under the current 
security context.

To get rid of this message and access the table from db3, you have to perform in this case 3 steps:

• The authenticator of the execution context under which I run the stored procedure is the owner of the db1 database, i.e. user1. Therefore db3 must trust user1. To achieve this, I'll create in db3 a user from the user1 login and I'll grant this user the AUTHENTICATE right.

USE db3;
CREATE USER user1 FOR LOGIN user1;
GO
GRANT AUTHENTICATE TO user1;

• Set the db1 database as TRUSTWORTHY, i.e. the SQL Server instance trusts this database and the content within it.

USE master;
ALTER DATABASE db1 SET TRUSTWORTHY ON

• Grant user1 the additional rights it needs in order to access Table3, in our case the SELECT right.

USE db3;
GO
GRANT SELECT ON Table3 TO user1;

Now the result will look like this:

The sys.user_token and sys.login_token views will show the execution context. Notice that user1 appears as authenticator of the execution context.

3. Signing the stored procedure with a certificate that exists in both databases.

For an easy to understand demo please see Erland Sommarskog's Giving Permissions through Stored Procedures

Notice that this time you only let a module of code access the other database. You don't have to trust the entire source database.

Next Steps

• Be sure you read this article, which provides the best description of this topic, otherwise it is not very easy to understand.
• I also recommend a more complex demo from Laurentiu Cristofor.
• Check K. Brian Kelley's security tips. They are among the best articles you can find on this subject.

About the author

Diana Moldovan is a DBA and data centric applications developer with 6 years of experience covering SQL 2000, SQL 2005 and SQL 2008.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips