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.

  • Does the object exist? I.e. does the table or proc that is being called exist? Both need to exist, obviously. Lastly, does app_user the login being used for the linked server, or other connection method to db_2? – scsimon Jul 11 '17 at 21:20
  • If an "application role" isn't involved, another factor that comes to mind is that it won't hurt to address an object using the schema name, and to square-quote the name parts, i.e. [DB_B].[dbo].[name of table]. Having said... is the object name shown in the message as "" ? That rings a bell about "linked server" operations, not quite relevant maybe, but, I think that I HAVE to name a linked server object with [dbo] not .. , AND that I wasn't allowed to truncate a table by four-part naming and/or by sending the Truncate statement to execute at the other end - except in stored procedure. – Robert Carnegie Jul 11 '17 at 21:59
  • 1
    Does app_user have permission to truncate that table? Having execute on the stored procedure is NOT enough in this case because ownership chaining doesn't span databases by default. (And enabling cross-database ownership chaining is a security nightmare) – AMtwo Jul 12 '17 at 1:27
  • The object does exist. If I run it with my login, it works, but I'm a sysadmin. It's not a linked server. The databases are on the same instance. – mqbk Jul 12 '17 at 2:44
  • @amtwo the user is the db_owner on the database on which the truncate is being executed – mqbk Jul 12 '17 at 2:48
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

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

  • Are you saying that you are using EXECUTE AS LOGIN=app_user where app_user is what the app is logging in as, and what the User in both DB_A and DB_B are created from? Or is it a different Login? – Solomon Rutzky Jul 12 '17 at 19:19
  • @SolomonRutzky They were using EXECUTE AS USER = app_user instead of using EXECUTE AS LOGIN = app_user. So, when executing the stored procedure, it was trying to pass the user permission between databases which we know will not happen. – mqbk Jul 17 '17 at 12:06
  • Right, but my questions are: 1) is the "app_user" in both of those cases the same Login / User? 2) Is it the same Login that the app is already connecting as? 3) if yes to #2, then is it even necessary to use EXECUTE AS since that was already the security context? – Solomon Rutzky Jul 17 '17 at 14:15
  • @SolomonRutzky They were trying using the EXECUTE AS because they wanted to see if the script would run with "app_user".They weren't trying it via the application they were running the code in SSMS first. – mqbk Jul 26 '17 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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