Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.
6 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

I have a two databases Source and 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 Target.

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.

Questions:

  1. What is special about SSMS that allows the cross-database trigger to work?

  2. What does "current security context" mean?

  3. 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 suggestion2nd 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.

I have a two databases Source and 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 Target.

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.

Questions:

  1. What is special about SSMS that allows the cross-database trigger to work?

  2. What does "current security context" mean?

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

I have a two databases Source and 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 Target.

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.

Questions:

  1. What is special about SSMS that allows the cross-database trigger to work?

  2. What does "current security context" mean?

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

5 attempting to implement srutzky's 2nd answer; still have trouble
source | link

I have a two databases Source and 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 Target.

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.

Questions:

  1. What is special about SSMS that allows the cross-database trigger to work?

  2. What does "current security context" mean?

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

I have a two databases Source and 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 Target.

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.

Questions:

  1. What is special about SSMS that allows the cross-database trigger to work?

  2. What does "current security context" mean?

  3. 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 have a two databases Source and 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 Target.

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.

Questions:

  1. What is special about SSMS that allows the cross-database trigger to work?

  2. What does "current security context" mean?

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

4 finish renaming user for clarity
source | link

I have a two databases Source and 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 Target.

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.

Questions:

  1. What is special about SSMS that allows the cross-database trigger to work?

  2. What does "current security context" mean?

  3. 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 'StandardUser''StandardUserRole', 'SourceDB';'SourceDBUser';

I am still getting the same error when attempting to update the table in SourceDB with the Application Role context.

I have a two databases Source and 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 Target.

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.

Questions:

  1. What is special about SSMS that allows the cross-database trigger to work?

  2. What does "current security context" mean?

  3. 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 'StandardUser', 'SourceDB';

I am still getting the same error when attempting to update the table in SourceDB with the Application Role context.

I have a two databases Source and 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 Target.

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.

Questions:

  1. What is special about SSMS that allows the cross-database trigger to work?

  2. What does "current security context" mean?

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

3 rename user for clarity
source | link
    Tweeted twitter.com/StackDBAs/status/845926262682107904
2 grammar
source | link
1
source | link