I am currently experiencing a problem with the implementation of a trigger on a SQL Server 2019 database server.
I have a trigger in table A1 of the DB_A database, it should do an insert into table B1 of the DB_B database when data is inserted into table A1 of the DB_A database.
Both databases are on the same instance.
This trigger works fine when I insert data with a user who has permissions directly on both databases.
The problem is that the application that connects to this database uses an application role to prevent users from making direct requests on the tables and when the trigger tries to insert into the remote table, I get the following error:
Msg 916, Level 14, State 2, Procedure XXX, Line 6 [Batch Start Line 1]
The server principal "XXXX" is not able to access the database "XXX" under the current security context.
The DB1 database has an application role that has rights only on that database, I know that application roles have a scope only at the database level, I also know that I can't map the application role to the other database but how do I solve this access problem between the databases of this same instance?
You have to give roles for both database to user then only you can insert data in both database