Cannot execute as the server principal
Question
Friday, November 29, 2013 3:46 PM
Hi,
I have restored a database from different server. The database has store procedure which run under an impersonated account IMPA. The impersonated account IMPA has permission to read data from other database.
Now, I can execute the procedure from certain accounts. But from other accounts it throws exception:
Msg 15406, Level 16, State 1, Procedure sp_NovusGetWithdrawalDetails, Line 19
Cannot execute as the server principal because the principal "ExactMetastorm" does not exist, this type of principal cannot be impersonated, or you do not have permission.
I can execute it from my account [Windows Authentication] with Admin rights as well as other account [SQL Login] with non-admin rights, but for rest of Windows Authentication, it does not work.
Any idea ?
Shady
All replies (4)
Saturday, November 30, 2013 5:01 PM ✅Answered
Hi Raminder,
This possibly is due to orphan user (SID mismatch).
To check this you can run the below:
Exec sp_change_users_login 'report'
this will report the list of orphan users.
Now run the below to fix that:
exec sp_change_users_login 'update_one','reported login','reported login'
OR
what you can do is script out the Logins with permissions on source server and run that on the restored database.
Hope this helps!!
Regards, Vishal Srivastava
Monday, December 2, 2013 6:51 AM ✅Answered
Hi,
I have done that already. I have fixed the orphan users. Also, even if I create a new user,it cannot execute the procedure. Not sure what right/Permissions I am missing?
Hi Raminder,
According to your description, we need to verify if the account of "ExactMetastorm" does exist in current server and can access your database, or when you create new account in server, you need to grant EXECUTE privileges of this SP to new account. If you want to impersonate the privileges using the stored procedures, I recommend that change the EXECUTE privileges of this SP to an account in the new server .
For more information about how to impersonate the privileges using the stored procedures, you can review the following article.
http://blogs.msdn.com/b/sqlserverfaq/archive/2009/07/24/how-to-impersonate-the-privileges-to-create-a-login-using-the-stored-procedures-using-execute-as-clause.aspx
There is a detail about how to move database between computers that are running SQL Server.
http://support.microsoft.com/kb/314546/en-us
Thanks,
Sofiya Li
Sofiya Li
TechNet Community Support
Friday, November 29, 2013 5:14 PM
I have restored a database from different server. The database has store procedure which run under an impersonated account IMPA. The impersonated account IMPA has permission to read data from other database.
Hello Shady,
Is that an SQL login and has it the same SID on both server or is it may be an orphaned user on the server where you have restore the database?
Olaf Helper
Sunday, December 1, 2013 9:40 AM
Hi,
I have done that already. I have fixed the orphan users. Also, even if I create a new user,it cannot execute the procedure. Not sure what right/Permissions I am missing?
Shady