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.

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.

  • Sorry it took me a sec to reply, I've set everything up on my personal rig and it looks like everything works (granted it worked without certificates on my rig already). However, I've set this up on my work server and still get the error I called out in the original question. Do I need to run execute as [PermissionedUser] on both A.CalProcInB and B.RunComponentsUpdate? – ErickM Mar 7 '17 at 19:12
  • Hi @ErickM . No, you don't want EXECUTE AS anywhere. And the reason it works locally for you is that you are running as sa or your Windows Login is in the Administrators group which is usually given the server-level role of sysadmin. But if you are using the EXECUTE AS clause in the A.CalProcInB proc, that will prevent this from working. – Solomon Rutzky Mar 7 '17 at 19:17
  • Ok, I do still get the error saying that user doesn't have permissions when I run without the "Execute as" clause. Quick run down of what I've done so far: -Created certificate in A -Created TempTable in A -Used Cert and Table in A to create Cert in B -edited both stored procedures on A and B to include "SET NOCOUNT ON" before executing rest of scripts -Used ADD SIGNATURE on both stored pocedures -ran code still got the error :/ – ErickM Mar 7 '17 at 19:27
  • @ErickM You aren't supposed to use the signature on the proc in Database B. It is only to create the User (i.e. Steps 5 and 6 above). – Solomon Rutzky Mar 7 '17 at 19:43
  • Haha, I just found out what went wrong, and the solution was much simpler than anticipated, I posted the solution as an answer. thanks for all your help though srutzky! – ErickM Mar 7 '17 at 20:15
up vote 3 down vote accepted

Turns out this solution was much simpler than anticipated. I had mentioned that there was a specified user activating the trigger to begin with, and that I had given that user "god" privileges.

Turns out, in my inexperience, I had given that user db_denywrite and db_denyread, thinking those were actually privileges that gave the user the ability to deny others from reading or writing to B.

However, this is opposite of the case since those "permissions" actually denied my god user from reading or writing to B. Unchecked the boxes and now everything works.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.