I have a two databases
Target hosted in the same SQL Server 2008 R2 instance. I have a Login which is mapped to Users in both databases. Each mapped User has a Role in its respective database which grants access to all objects in that database.
I have a cross-database trigger which propagates updates from a table in
Source to a table in
When I connect to the server in SQL Server Managment Studio with the Login and run an
update query on
Source, the cross-database update succeeds.
But when an equivalent update is initiated using the same Login from within an application, the trigger fails with error:
The server principal 'name' is not able to access the database 'Target' under the current security context.
By running a trace with SQL Server Profiler, I can verify that the
update is being run under the same LoginName in both cases.
What is special about SSMS that allows the cross-database trigger to work?
What does "current security context" mean?
What do I need to modify to allow the update+trigger to work in the Application's context?
I've read about object ownership chaining, and I've not fully investigated that yet. But since it works in SSMS, I'm tempted to believe that a broken ownership chain is not the issue. But I could be wrong about that! I'd be really grateful for any diagnosis suggestions.
Update: I'm attempting to implement srutzky's suggestion. This is what I have run so far.
use [SourceDB]; create certificate [Access_TargetDB] encryption by password = 'password123' with subject = 'Cross-DB Access to TargetDB', expiry_date = '2099-12-31'; add signature to [MyTrigger] by certificate [Access_TargetDB] with password = 'password123'; backup certificate [Access_TargetDB] to file = 'C:\Access_TargetDB.cert'; use [TargetDB]; create certificate [SourceDB] from file = 'C:\Access_TargetDB.cert'; create user [SourceDBUser] for certificate [SourceDB]; exec sp_addrolemember 'StandardUserRole', 'SourceDBUser';
I am still getting the same error when attempting to update the table in
SourceDB with the Application Role context.
I'm attempting to implement srutzky's 2nd suggestion. In addition to the above, I have also run the following. But the suggestion assumes a target stored procedure which I do not have, so maybe this isn't actually a valid test.
use master; create certificate [SourceDB] from file = 'C:\SourceDB.cert'; create login [SourceDBLogin] from certificate [SourceDB]; grant authenticate server to [SourceDBLogin];
Still no change in the permissions available in the Application Role context.