Sql-server – Permissions not allowing Update or Select on a trigger executed function

permissionssql serversql server 2014stored-procedurestrigger

With two databases A and B on the same SQL Server 2014 instance, I've written a series of scripts trying to do the following (written in very high level pseudo code):

  1. User Updates table A.dbo.Main

  2. A.dbo.Main has a trigger on UPDATE, DELETE, INSERT that calls a stored procedure A.CallProcInB

  3. A.CallProcInB then calls a stored Procedure in B called B.RunComponentsUpdate

  4. B.RunComponentsUpdate then merges a view and a table called B.dbo.A_View and B.dbo.B_Table.

The problem is I have gotten these series of scripts to work perfectly on a personal computer running SQL Server 2014 Express, with the exact same schema setup for the databases A and B, but when I set everything up on my company's main server running SQL Server 2014 I get the following error message:

System.Data.Odbc.OdbcException (0x80131937):
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
The SELECT permission was denied on the object 'B_Table', database 'B', schema 'dbo'.
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
The UPDATE permission was denied on the object 'B_Table', database 'B', schema 'dbo'.

I've tried giving the user activating the initial trigger all privileges on both A and B, as this was enough to fix the issue on my personal computer server. However, giving basically "god" privileges hasn't been enough to resolve this error on the server.

Best Answer

The permissions error is to be expected when you don't have things like Cross-Database Ownership Chaining and TRUSTWORTHY enabled, and aren't logging in as sa ;-).

This very similar to the problem described (and resolved) in my answer to another question here on DBA.SE:

Permissions in triggers when using cross database certificates

However, in that case there was a Trigger in Database B and that is not the situation here. Still, this should be fairly easy to solve and will use a setup similar to what is described in that other answer.

Here are the steps you need to do for your particular issue (all of which are shown in that other answer):

  1. Create a Certificate in Database A.
  2. Sign the A.dbo.CallProcInB Stored Procedure using that Certificate.
  3. Backup the Certificate, either to a File or into a temporary table (as shown in that linked answer).

  4. Create that same Certificate in Database B (which is done by getting it from the backup file or out of the temp table, as shown in that linked answer).
  5. Create a User from that Certificate.
  6. Grant EXECUTE on the B.dbo.RunComponentsUpdate Stored Procedure to the new Certificate-based User.

That should be all you need.

Please be aware that any changes to the A.dbo.CallProcInB Stored Procedure will cause it to lose the signature. In those cases, just execute ADD SIGNATURE again.

Related Question