Getting execute permission to xp_cmdshell

by

For users that are not members of the sysadmin role on the SQL Server instance you need to do the following actions to grant access to the xp_cmdshell extended stored procedure. In addition if you forgot one of the steps I have listed the error that will be thrown.

  1. Enable the xp_cmdshell procedure

    Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
    SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.*

  2. Create a login for the non-sysadmin user that has public access to the master database

    Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
    The EXECUTE permission was denied on the object ‘xp_cmdshell’, database ‘mssqlsystemresource’, schema ‘sys’.*

  3. Grant EXEC permission on the xp_cmdshell stored procedure

    Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
    The EXECUTE permission was denied on the object ‘xp_cmdshell’, database ‘mssqlsystemresource’, schema ‘sys’.*

  4. Create a proxy account that xp_cmdshell will be run under using sp_xp_cmdshell_proxy_account

    Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
    The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the ‘##xp_cmdshell_proxy_account##’ credential exists and contains valid information.*

It would seem from your error that either step 2 or 3 was missed. I am not familiar with clusters to know if there is anything particular to that setup.

More Related Contents:

  1. Updating image in VB and SQL Server not working [closed]
  2. Query to list number of records in each table in a database
  3. Storing a file in a database as opposed to the file system?
  4. Multiple Indexes vs Multi-Column Indexes
  5. Preferred Method of Storing Passwords In Database
  6. Using a sort order column in a database table
  7. Create a view with ORDER BY clause
  8. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  9. How to do version control for SQL Server database?
  10. What columns generally make good indexes?
  11. How to determine total number of open/active connections in ms sql server 2005
  12. How to check if a database exists in SQL Server?
  13. TSQL: Create a view that accesses multiple databases
  14. How to migrate a PostgreSQL database into a SQLServer one?
  15. What is the point of “Initial Catalog” in a SQL Server connection string?
  16. “Cannot open user default database. Login failed.” after installing SQL Server Management Studio Express
  17. How to create a yes/no boolean field in SQL server?
  18. Best way to store time (hh:mm) in a database
  19. How can I run just the statement my cursor is on in SQL Server Management Studio?
  20. Executing set of SQL queries using batch file?
  21. Database design: Calculating the Account Balance
  22. In SQL Server, how can I lock a single row in a way similar to Oracle’s “SELECT FOR UPDATE WAIT”?
  23. Date range overlapping check constraint
  24. What are the advantages of VistaDB
  25. Difference between database and schema
  26. Generate table relationship diagram from existing schema (SQL Server) [closed]
  27. Calculate Time Difference Between Two Rows
  28. What are the limitations to SQL Server Compact? (Or – how does one choose a database to use on MS platforms?)
  29. How do you deal with blank spaces in column names in SQL Server?
  30. Tool for Scripting Table Data

Leave a Comment