I need to be able to run xp_cmdshell from a sp in my database. This runs fine for me, as I'm a sysadmin user, but when the actual user, who is not, tries to run it, it fails.
So I created a sql server login call xp_cmdshell_user which has sysadmin rights and I'm trying to switch the login to that user as my first step in my sp using the following:
EXECUTE AS LOGIN = 'xp_cmdshell_user'
When I run the sp as myself, again no problem.
When I run it as a non-sysadmin user I get the following error:
Msg 15406, Level 16, State 1, Procedure process_Lab_Load, Line 21
Cannot execute as the server principal because the principal "xp_cmdshell_user" does not exist, this type of principal cannot be impersonated, or you do not have permission.
I understand I need to set some sort of impersonation but I can't seem to get this to work with a windows login. The current login trying to run the sp is MyDomain\TestUser.
I tried the following command:
GRANT IMPERSONATE ON USER:: 'MyDomain\TestUser' TO 'xp_cmdshell_user'
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'MyDomain\TestUser'.