2

I have two databases DB_A and DB_B. From DB_A the developers are calling a stored procedure that will truncate table_a in DB_B. When they execute the stored procedure using the login from the application, we will call this "app_user", it returns the error message. Cannot find object "" because it does not exist or you do not have permissions.

Now my first instinct is to check the permissions for app_user on both DB_A and DB_B. The user is a db_owner on the latter. I ran the sp_change_users_login to see if the database user was orphaned, and it was not. I am not exactly sure why I am still getting this error. Any help will be appreciated.

Solomon Rutzky
  • 70,048
  • 8
  • 160
  • 306
mqbk
  • 53
  • 1
  • 1
  • 5

2 Answers2

1

This issue here is that it doesn't so much matter what the User in DB_B is because Database-level permissions, by default, do not transfer between Databases. There are ways of getting this to work that require little effort, but that are also huge security risks: enabling Cross-Database Ownership Chaining and/or enabling the TRUSTWORTHY Database property. But you don't need either of those. Instead, what you can do is:

  1. Create a Certificate in DB_A
  2. Sign the Stored Procedure in DB_A with that Certificate
  3. Extract the Certificate bytes and Private Key bytes using the built-in functions: CERTENCODED and CERTPRIVATEKEY
  4. Create that same Certificate in DB_B using the extracted Certificate and Private Key bytes
  5. Create a User in DB_B from that Certificate
  6. Add the Certificate-based User in DB_B to the db_owner Database Role.

Please see the following answer of mine on a related question that shows this working between two databases:

Giving Special Permissions to a Stored Procedure in SQL Server

Solomon Rutzky
  • 70,048
  • 8
  • 160
  • 306
0

Thanks for this response; however the route to the answer was much simpler. I am almost embarrassed to even mention what was the error, but if this will help someone else, it is well worth it. Apparently the procedure was being called with a EXECUTE AS USER = app_user statement. As mentioned before, the "database users permissions" are not transferable. When run as EXECUTE AS LOGIN, it worked. Also when run logged into ssms as the login it works as well. Thank you everyone for your response

mqbk
  • 53
  • 1
  • 1
  • 5