EXECUTE permission denied on object 'xp_cmdshell' | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

EXECUTE permission denied on object ‘xp_cmdshell’

I have a user id witch is a member of public, db_datareader and db_datawriter. I want to run the following comand in QA.
exec @I=master.dbo.xp_cmdshell ‘dtsrun /S 1.2.3.4Test /E /N Import_DTS’
And I get the following error: Server: Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 12
EXECUTE permission denied on object ‘xp_cmdshell’, database ‘master’, owner ‘dbo’. I remember it was working with this user id but now it is not. It works when I use a user id with DBO membership. What’s the problem? CanadaDBA
I found this in BOL:

Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users. Important If you choose to use a Windows NT account that is not a member of the local administrator’s group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell.

CanadaDBA
relaxing the permissions for xp_cmdshell is very dangerous though… there is nothing stopping that user running
exec xp_cmdshell ‘del c:. /s /f /q’
or some equally catastrophic command… Cheers
Twan
]]>