xp_cmdshell stored procedure

xp_cmdshell

The extended stored procedure xp_cmdshell allows you to shell out and execute an valid operating system command. By default, all users allocated to the fixed system role sysadmin (i.e. SA account) have execute access. This is a real tough command to administer. Why? Every project I have worked on to date has some need for it. Because of the fact that SA is the only user with sysadmin access, rather than creating a special account or looking at other work-arounds, the SA account is used to execute the task. Classic examples are stored procedures wrapped up in DTS jobs or supposed global "administrative" functions. This is problematic because:

Now "application" databases are using the SA account and rely on it to run their jobs.

Altering the SA password has application impact

Xp_cmdshell will be executed under the security context in which the SQL Server service is running

Points a) and b) are obvious and the fix is a simple matter of explicitly granting execute access to xp_cmdshell via a role and allocating that role to a managed and secure database user whose actual login is rarely used (i.e. DTS connectivity only to run the command shell tasks).Point c) is the very important. If the database user is a member of sysadmin then, more than likely, the user will have "local administrative" privileges to the server, as this is the user running the MS SQL Server and associated services. This is even more the reason why not to use the SA account. If the user is not a sysadmin but has been granted execute access as described above, then the SQL Server Agent proxy service user will be used instead.

The SQL Server Agent proxy account can be altered via xp_sqlagent_proxy_account (undocumented in BOL), which defines the account used to run the SQLServerAgent service. This may be your SQLServer NT user if you are not using the Administrator account (which is bad security practice), so you may decide to alter this to another user with restricted access rights for finer control to the operating system.

In the end, xp_cmdshell should be carefully evaluated before using it. Ideally it should be totally disabled (revoke execute permission) for ultimate security. Look at use isql jobs scheduled via NT and look closer at the options available to you via DTS.

Example: Stored Procedure to delete files in a folder

When your only way of managing SQL Server is through Enterprise Manager or Query Analyzer, you will have to do a lot of tasks through command shell. If you find yourself routinely conduct some activities using xp_cmdshell, it is a good idea to wrap those routines into a stored procedure. This example takes an input parameter for directory name and delete all files within that directory. The directory name can be UNC path. I put some comments within this procedure so it should be easy to follow.

CREATE proc usp_DeleteFileInFolder
@FolderName varchar(150)
AS

SET NOCOUNT ON

declare @DOSCommand varchar(150)
--Check whether the user supply \ in the directory name

if not (right(@FolderName, 1) = '\')
set @FolderName = @FolderName + '\'

--Delete all files within this folder. Note: del *.* only deletes files,
--not folders. Q is the quite switch so DOS will not ask for confirmation

set @DOSCommand = 'del /Q ' + '"' + @FolderName + '*.*' + '"'
print @DOSCommand
exec master..xp_cmdshell @DOSCommand
GO