Getting execute permission to xp cmdshell

Question

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

User · Answer

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    create new role   use master CREATE ROLE  CmdShell Executor  AUTHORIZATION  dbo  GRANT EXEC ON xp cmdshell TO  CmdShell Executor  add users in master database  Security --  Users  Membership checks only  CmdShell Executor  that is just created 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 · Answer

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 · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   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  quot Surface Area Configuration quot  in SQL Server Books Online     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      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      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

[sql-server] Getting execute permission to xp_cmdshell

Examples related to sql-server

Examples related to database

Examples related to xp-cmdshell