Get execute permission for xp_cmdshell
I see an error when I try to execute xp_cmdshell from a stored procedure.
xp_cmdshell is included in the instance. And execute permission was granted to my user, but I still see the exception.
EXECUTE permission was denied on object 'xp_cmdshell, database' mssqlsystemresource, schema 'sys
Part of the problem is that this is a shared cluster and we have a single database for the instance, so we don't have full administrator permission. So I can't get in and grant permissions and what not.
source to share
For users who are not members of the sysadmin role in an instance of SQL Server, you need to follow these steps to provide access to the xp_cmdshell extended stored procedure. Also, in case you forgot one of the steps, I have listed the error to be thrown.
-
Include procedure xp_cmdshell
Msg 15281 Level 16 State 1 Procedure xp_cmdshell Line 1 SQL Server blocked access to procedure "sys.xp_cmdshell" in component "xp_cmdshell" because this component was disabled as part of the security configuration for this server. The system administrator can enable the use of "xp_cmdshell" with sp_configure. For more information about enabling "xp_cmdshell", see "Surface Configuration" in SQL Server Books Online. *
-
Create a login for a non-sysadmin user who has public access to the main database
Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1 EXECUTE permission was denied for object "xp_cmdshell", database "mssqlsystemresource", schema "sys". *
-
Grant EXEC permission on stored procedure xp_cmdshell
Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1 EXECUTE permission was denied for object "xp_cmdshell", database "mssqlsystemresource", schema "sys". *
-
Create a proxy account that will run xp_cmdshell using sp_xp_cmdshell_proxy_account
Msg 15153 Level 16 State 1 Procedure xp_cmdshell Line 1 The xp_cmdshell proxy account information could not be restored or is invalid. Make sure the credentials '## xp_cmdshell_proxy_account ##' exist and contain valid information. *
By your mistake, it would seem that either step 2 or 3 was skipped. I am not familiar with clusters to see if there is anything special for this setup.
I want to follow the answer from tchester.
(1) Include procedure xp_cmdshell:
-- 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 a non-sysadmin user who has public access to the main 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 will run xp_cmdshell 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) Allowing Grant Management Server for User
USE master;
GRANT CONTROL SERVER TO [Domain\TestUser]
GO
source to share
tchester said:
(2) Create a login for a non-sysadmin user who has public access to the main database
I went to the list of user databases (server / security / connections / my username / properties / user mapping and wanted to check the box for the master database. I got an error saying the user already exists in the master database. To run the database, dropping the user, go back to "user mapping" and check the box for the wizard. See "public" below.
After that you need to re-submit the execution grant on xp_cmdshell to "my username"
Yves
source to share
To extend access to what was provided to automatically export data as csv to a network share using SQL Server Agent.
(1) Include procedure xp_cmdshell:
-- 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 a non-sysadmin user who has public access to the main database. Done with custom mapping
(3) Give the login as a batch job. Go to Local Security Policy → Local Policies → User Rights Assignment. Add user to "Login as Batch Job"
(4) Read / write permissions on 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 where xp_cmdshell will run when using sp_xp_cmdshell_proxy_account
EXEC sp_xp_cmdshell_proxy_account 'Domain\TestUser', 'password_for_domain_user'
(7) If sp_xp_cmdshell_proxy_account command doesn't work, create it manually
create credential ##xp_cmdshell_proxy_account## with identity = 'Domain\DomainUser', secret = 'password'
(8) Enable SQL Server Agent. Open SQL Server Configuration Manager, go to SQL Server Services, enable SQL Server Agent.
(9) Create an automatic job. Open SSMS, select SQL Server Agent, then right click and click New Job.
(10) Select "Owner" as your created user. Select "Steps", enter "type" = T-SQL. Fill in the command field as shown below. Set separator as ','
EXEC master..xp_cmdshell 'SQLCMD -q "select * from master" -o file.csv -s ","
(11) Fill in the graphs accordingly.
source to share
The time to contribute is now. I am a sysadmin role and have been working on getting two open access users to execute xp_cmdshell. I can execute xp_cmdshell but not two users.
I did the following steps:
create a new role:
use wizard
CREATE ROLE [CmdShell_Executor] AUTHORIZATION [dbo]
GRANT EXEC ON xp_cmdshell TO [CmdShell_Executor]add users to the main database: Security → Users. Membership only checks [CmdShell_Executor] which has just been created
set up proxy account:
EXEC sp_xp_cmdshell_proxy_account 'domain \ user1', 'Windows1 user password'
EXEC sp_xp_cmdshell_proxy_account 'domain \ user2', 'users2 Windows password'
Both users can then execute a stored procedure containing xp_cmdshell that invokes the R script to run. I allowed users to come to my PC to enter a password, execute a one-line code, and then remove the password.
source to share