SQL Server Performance

EXECUTE permission denied on object 'xp_cmdshell'

Discussion in 'SQL Server DTS-Related Questions' started by CanadaDBA, Oct 26, 2004.

  1. CanadaDBA New Member

    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 /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?

  2. CanadaDBA New Member

    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.

  3. Twan New Member

    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...


Share This Page