Search code examples
sqlsql-servert-sqldatabase-trigger

Is there a way to default the trigger to run on specific user for DML operations?

I have a trigger on Table A in Database A which inserts data into Table b which is in Database B, both databases are on the same server.

A user with developer access to Table A in Database A is encountering an error when they perform dml operations as his role is restricted just to use Database A. Is there a way to default a trigger to run on a specific connection?

Solution

  • In SQL Server, triggers (and stored procedures and functions) have the EXECUTE AS clause. This gives you control over the permissions when the code is executed.