Getting execute permission to xp_cmdshell

user_245097 0
Viewed 214586

I am seeing an error message when trying to execute xp_cmdshell from within a stored procedure.

xp_cmdshell is enabled on the instance. And the execute permission was granted to my user, but I am still seeing the exception.

The EXECUTE permission was denied on the object ‘xp_cmdshell’, database ‘mssqlsystemresource’, schema ‘sys’

Part of the issue is that this is a shared cluster, and we have a single database on the instance, so we don't have a full range of admin permissions. So I can't go in and grant permissions, and what-not.

xp-cmdshell sql-server database
6 Answers

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.

Admin1

Time to contribute now. I am sysadmin role and worked on getting two public access users to execute xp_cmdshell. I am able to execute xp_cmdshell but not the two users.

I did the following steps:

  1. create new role:

    use master
    CREATE ROLE [CmdShell_Executor] AUTHORIZATION [dbo]
    GRANT EXEC ON xp_cmdshell TO [CmdShell_Executor]

  2. add users in master database: Security --> Users. Membership checks only [CmdShell_Executor] that is just created

  3. set up proxy account:

    EXEC sp_xp_cmdshell_proxy_account 'domain\user1','users1 Windows password'
    EXEC sp_xp_cmdshell_proxy_account 'domain\user2','users2 Windows password'

Then both users can execute the stored procedure that contains xp_cmdshell invoking a R script run. I let the users come to my PC to type in the password, execute the one line code, then delete the password.

user_57542140

Don't grant control to the user, it's totally unnecessay. Select permission on the database is enough. After you have created the login and the user on master (see above answers):

use YourDatabase
go
create user [YourDomain\YourUser] for login [YourDomain\YourUser] with default_schema=[dbo]
go
alter role [db_datareader] add member [YourDomain\YourUser]
go
user_48538760
Related