Ownership Chaining

Last Post 13 Dec 2011 05:18 AM by rm. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Not Resolved
New Member
New Member

12 Dec 2011 02:53 PM
I am testing a process to retrieve data from a secondary server, using Windows authentication, and the only permissions assigned to UserA is EXECUTE PROCEDURE.  Database chaining is turned on each of the databases.

1. UserA is granted connect to SourceServer.SrcDB database
2. UserA is granted execute to SourceServer.SrcDB.dbo.Proc1 procedure
3. UserA is granted connect to TargetServer.TgtDB1 database
4. UserA is granted connect to TargetServer.TgtDB2 database
5. UserA is granted execute to TargetServer.TgtDB1.dbo.Proc2 procedure
6. UserA on SourceServer executes stored procedure SourceServer.SrcDB.dbo.Proc1 which executes the TargetServer.TgtDB1.dbo.Proc2 procedure (this procedure includes Select access to data in TargetServer.TgtDB2  database

We are trying to limit the specific table permissions for access to data on a second server.  If the UserA is a sysadmin on the TargetServer all is good.  If I remove the sysadmin privilege, then no data is returned.

Is it possible to access data across servers using only execute procedure permissions?  If so, is there a way that someone can explain to me how to get this done?  I initially tested this process, but didn't realize that my access as a sysadmin was giving me what I needed.

New Member
New Member

13 Dec 2011 05:18 AM
I guess you do that via linked server, how did you set security in linked server? Did you enable ownership chain between TgtDB1 and TgtDB2 on TargetServer? Proc2 and referenced table on TargetServer are in same schema?

Acceptable Use Policy