SQL Server Login to access tables of three different database from single database

Question

I have created a login in SQL Server 2008 R2 and given access to run stored procedures in that database only. But this stored procedure is actually accessing data from other two databases to which this user doesn't have access and I cannot give access to those databases.

How can I create such a user with single database access but can run query on other two database through this stored procedure only?

So far what I have done:

1. Created login and mapped to Database1

2. Right click on the stored procedure in Database1 and given permission to this user to execute this stored procedure

3. This stored procedure is running a query to get data from two other databases (Database2 and Database3)

   e.g.

   Select value1, value2, value3 
   from Database1.[dbo].table1 
   inner join Database2.[dbo].table2 on .... 
   inner join Database3.[dbo].table3 on ....
   

Answer 1

Cross-database ownership chaining is the easier solution. But as Oliver noted, it does come with some security exposures.

If you want to avoid the cross-database ownership chaining approach you can, to quote Erland Sommarskog: "When you need to write a stored procedure that accesses data in another database, you can arrange permissions by signing your procedure with a certificate that exists in both databases."

Erland has a lengthy article on the subject. I am giving you the link to the specific issue of cross database permissions using a certificate: http://www.sommarskog.se/grantperm.html#certcrossdb

This is safer, but it is more work.

Answer 2

Database ownership chaining will achieve what you are looking for as long as your SP and the tables in the destination DB are owned by users of the same name.

Just alter all participating databases so they are allowed to do ownership chaining:

ALTER DATABASE database1 SET DB_CHAINING ON
ALTER DATABASE database2 SET DB_CHAINING ON
ALTER DATABASE database3 SET DB_CHAINING ON

Additionally, you have to grant the logins executing the SP the right to access the destination database:

USE database2;
EXEC sp_grantdbaccess 'loginname'
USE database3;
EXEC sp_grantdbaccess 'loginname'

Please read about the security implications this has: http://technet.microsoft.com/en-us/library/ms188676(v=sql.105).aspx

In short, don't use ownership chaining when you can't trust the db_owner of the source database.

For a good in-detail explanation including examples see http://www.mssqltips.com/sqlservertip/1782/understanding-cross-database-ownership-chaining-in-sql-server/