SQL Server Login to access tables of three different database from single database 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:


Created login and mapped to Database1
Right click on the stored procedure in Database1 and given permission to this user to execute this stored procedure
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 ....
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/