I recently had a requirement for an application account to run a stored procedure that utilized xp_cmdshell. The first question is can the functionality be accomplished without enabling xp_cmdshell, unfortunately not. The next question was OK, now how to do that without granting sysadmin to the application service domain account.
Here’s what I came up with it allows for the delegation of rights to execute the xp_cmdshell functionality indirectly via a stored procedure without conveying the rights to directly execute xp_cmdshell to the user account.
1) The first step is to create a domain account to act as the proxy account. For demonstration purposes lets call it “yourDomain\TaskRunner
2) Now we must build the Login and proxy account using the domain account created in the previous step. USE [master];
CREATE LOGIN [yourDomain\TaskRunner] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];
CREATE USER [yourDomain\TaskRunner] FOR LOGIN [yourDomain\TaskRunner] WITH DEFAULT_SCHEMA=[dbo];
EXEC sp_xp_cmdshell_proxy_account ‘yourDomain\TaskRunner’,’thep@ssw0rd’;
3) Next we must grant rights to execute xp_cmdshell GRANT EXECUTE ON xp_CmdShell to [yourDomain\TaskRunner]; GO — check the results EXEC sp_helpuser [ITIC\task.runner1];
4) Create store procedure utilizing xp_cmdshell.
— CREATE PROCEDURE XYZ
— WITH EXECUTE AS OWNER
— EXEC xp_cmdshell ‘DIR C:\’;
5) Finally grant execute to your stored procedure to a database user, database role, or public.
GRANT EXECUTE ON dbo.XYZ TO PUBLIC;
— If you get the following error:
— The server principal "sa" is not able to access the database under the current security context.
— Then mark the databases involved in the stored proc as Trustworthy
— ALTER DATABASE xyz SET TRUSTWORTHY ON