An extended stored procedure called xp_cmdshell causes SQL Server to spawn a command shell and execute the command given as a parameter. For example, xp_cmdshell ‘dir c:mssqlackup’ would return a listing of the files in the backup directory. In general, this utility is useful for administrators. What you must be aware of is that the command executes with the privileges of the account under which SQL Agent executes. Since this account is typically a member of the administrator group, a user could wreak tremendous havoc (“I didn’t realize that format c: would cause any problems! Really!”).
To limit this command to administrators, Right click on the SQL Server Agent Icon in Enterprise Manager and choose “properties” from the menu. Choose the job system tab. At the bottom there will be a checkbox next to text that reads “Only users with Sysadmin privileges can execute CmdExec and ActiveScripting jobs here.” Make sure the checkbox is filled in.
