0

I have restored a database to a different server. The following errors do not occur in the original database.

I have a username WITHOUT LOGIN. Username is used in EXECUTE AS for a procedure. In the restored database, when attempting to execute the stored procedure, I get the following error

Cannot execute as the user 'username', because it does not exist or you do not have permission.

This is NOT a traditional orphaned user because it is WITHOUT LOGIN. It acts AS IF it is orphaned. But, The user cannot be re-aligned with a LOGIN because there is no LOGIN to realign. In researching, I find this question asked and all responses reference orphaned users. For example, https://stackoverflow.com/questions/798206/error-with-sql-server-execute-as

This Microsoft reference also pops up with the above error, but it is purely orphaned users with LOGINs. https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server?redirectedfrom=MSDN&view=sql-server-ver16

How might I resolve this issue?

4
  • Do you have permission to impersonate the user? Oct 21, 2022 at 0:22
  • Yes. The user calling the stored procedure has permissions.
    – jim
    Oct 21, 2022 at 12:38
  • Run this query to verify the proc execution context user is as expected: SELECT USER_NAME(execute_as_principal_id) FROM sys.sql_modules WHERE object_id = OBJECT_ID(N'dbo.YourProc');
    – Dan Guzman
    Oct 21, 2022 at 21:28
  • @DanGuzman That rocks!! Thank you. Yes, it shows the correct value for USER_NAME(execute_as_principal_id)
    – jim
    Oct 22, 2022 at 22:28

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.