3

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.

Paul White
  • 83,961
  • 28
  • 402
  • 634
ErickM
  • 73
  • 2
  • 8

2 Answers2

3

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.

Community
  • 1
Solomon Rutzky
  • 68,731
  • 8
  • 152
  • 292
  • 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 07 '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 07 '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 07 '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 07 '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 07 '17 at 20:15
  • @ErickM Ok. I see that. BUT, you should not be using a "god" user / login as it is a huge security risk. It is like putting all Windows Users in the Administrators group. Your system will be A LOT more secure by implementing what I outlined above (which will work with low-privileged users now that you removed the deny). – Solomon Rutzky Mar 07 '17 at 20:26
  • Alright, I'll keep that in mind going forward and will let the main DBA know about the security hole. I'm an electrical engineer by career so I hope somebody else in the company will take this over once it's working and clean it up as I'm not particularly trained for this. thanks again! – ErickM Mar 07 '17 at 20:29
  • @ErickM Understood. Hopefully your DBA will already be aware of the implications of such a privileged account and not allow it. I guess you'll find out ;-). Good luck! – Solomon Rutzky Mar 07 '17 at 20:42
2

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.

Paul White
  • 83,961
  • 28
  • 402
  • 634
ErickM
  • 73
  • 2
  • 8