Sql-server – Cannot find the object because it does not exist

permissionssql serversql-server-2012

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.

Best Answer

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

Related Question