Sometimes there comes a time when you would like to have a user of your database be able to execute a program by making use of SQL Server’s xp_cmdshell procedure.  It can be a little tricky setting up the permissions to enable this call, so here are some steps you can take to make it possible.

Firstly, allow execution of xp_cmdshell:

USE [master]
GO
EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE 
GO

Secondly, create a SQL Login that can run xp_cmdshell.  Please create a more secure password than the example given:

USE [master] 
GO
CREATE LOGIN [CommandShellLogin] WITH PASSWORD ='abc123!@#' 
GO
CREATE USER [CommandShellLogin] FROM LOGIN [CommandShellLogin] 
GO
GRANT EXECUTEON xp_cmdshell TO [CommandShellLogin] 
GO

Thirdly, because you will be switching to this command shell login temporarily, grant impersonation rights to the user’s group. Note: Am assuming that the user group is mapped to a login in SQL Server:

USE [master] 
GO
GRANT IMPERSONATE ON LOGIN::CommandShellLogin TO [DOMAIN\USER GROUP] 
GO

Now, you could make [CommandShellLogin] a member of sysadmin to make this work, but that would be providing too much priviledges. Instead, you can set up a proxy account that xp_cmdshell uses whenever it is called. This will be an actual windows account that the command will execute as, so make sure that the windows account has the permissions to perform whatever it is you are intending it to execute:

EXEC sp_xp_cmdshell_proxy_account 'DOMAIN\User', 'UserPassword' 
GO

Now in your procedure, trigger, script or whereever you plan on running xp_cmdshell, switch the context to the CommandShellLogin user temporarily to execute your desired command. Here we are just running DIR as an example:

EXECUTE AS LOGIN = 'CommandShellLogin' 
EXEC xp_cmdshell 'DIR' 
REVERT

Note: Setting up the proxy account above results in any xp_cmdshell to run as the user specified. If you no longer want subsequent calls to xp_cmdshell to run as that windows account, then ensure that you empty the proxy account so that it does not run anymore as that account:

EXEC sp_xp_cmdshell_proxy_account NULL 

Hope this helps,

Stefan.

For more Web Code, ASP.NET, SQL Server and other development tips, please check back here at webcodeblog.com often.