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.
Jacki |