[sql-server] Getting execute permission to xp_cmdshell

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.

To expand on what has been provided for automatically exporting data as csv to a network share via SQL Server Agent.

(1) Enable the xp_cmdshell procedure:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

-- Enable the xp_cmdshell procedure
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

(2) Create a login 'Domain\TestUser' (windows user) for the non-sysadmin user that has public access to the master database. Done through user mapping

(3) Give log on as batch job: Navigate to Local Security Policy -> Local Policies -> User Rights Assignment. Add user to "Log on as a batch job"

(4) Give read/write permissions to network folder for domain\user

(5) Grant EXEC permission on the xp_cmdshell stored procedure:

GRANT EXECUTE ON xp_cmdshell TO [Domain\TestUser]

(6) Create a proxy account that xp_cmdshell will be run under using sp_xp_cmdshell_proxy_account

EXEC sp_xp_cmdshell_proxy_account 'Domain\TestUser', 'password_for_domain_user'

(7) If the sp_xp_cmdshell_proxy_account command doesn't work, manually create it

create credential ##xp_cmdshell_proxy_account## with identity = 'Domain\DomainUser', secret = 'password'

(8) Enable SQL Server Agent. Open SQL Server Configuration Manager, navigate to SQL Server Services, enable SQL Server Agent.

(9) Create automated job. Open SSMS, select SQL Server Agent, then right-click jobs and click "New Job".

(10) Select "Owner" as your created user. Select "Steps", make "type" = T-SQL. Fill out command field similar to below. Set delimiter as ','

EXEC master..xp_cmdshell 'SQLCMD -q "select * from master" -o file.csv -s "," 

(11) Fill out schedules accordingly.

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.

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

I want to complete the answer from tchester.

(1) Enable the xp_cmdshell procedure:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

-- Enable the xp_cmdshell procedure
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

(2) Create a login 'Domain\TestUser' (windows user) for the non-sysadmin user that has public access to the master database

(3) Grant EXEC permission on the xp_cmdshell stored procedure:

GRANT EXECUTE ON xp_cmdshell TO [Domain\TestUser]

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

EXEC sp_xp_cmdshell_proxy_account 'Domain\TestUser', 'pwd'
-- Note: pwd means windows password for [Domain\TestUser] account id on the box.
--       Don't include square brackets around Domain\TestUser.

(5) Grant control server permission to user

USE master;
GRANT CONTROL SERVER TO [Domain\TestUser]
GO

tchester said :

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

I went to my user's database list (server/security/connections/my user name/properties/user mapping, and wanted to check the box for master database. I got an error message telling that the user already exists in the master database. Went to master database, dropped the user, went back to "user mapping" and checked the box for master. Check the "public" box below.

After that, you need to re-issue the grant execute on xp_cmdshell to "my user name"

Yves