Jtdylktuy

From SQL server 2012 I try to do this:

DECLARE @COMMAND nvarchar(4000)

SET @COMMAND = 'net stop <servicename>'

exec master.dbo.xp_cmdshell @COMMAND

I get system error 5 and Access denied as response

The service account (checked with whoami) is added to the administrators, so what else can be wrong?

Run SQL Server 2012 as administrator, and the problem will go away.

Allow non-sysadmin user to execute xp_cmdshell from SSMS

I helped troubleshoot an issue where we needed to grant an app developer access to execute xp_cmdshell from within an SSMS session rather than "Run as" from SQL Agent Job on a non-critical development server without making him a sysadmin on the SQL Server instance.

What We Did

Note: Domain user account can be replaced by work group account, local machine account, etc.

Important: You need to understand the risk of allowing users in your environment to execute OS level commands, and you should only grant this level of permissions to xp_cmdshell to those which are trustworthy enough with this level of security with the proxy method.

1. Created a new domain user account with a strong password and took note of the username and password. Made sure the account was enabled and the password was set to never expire.




2. Created a new SQL Server Login tied to the new domain user account we created.



3. 
   

   Created a new proxy credential tied to the domain user account.

   
   
   

   
   

   EXEC sp_xp_cmdshell_proxy_account '<Domain><NewUser>', '<password>' -- you have to type actual password
   
   

   
   

   
   


4. 
   

   Granted that SQL Server Login explicit EXECUTE access to the system extended stored proc from the Master DB named sys.xp_cmdshell.

   
   
   

   
   

   --see who all has execute access to xp_cmdshell   
   
   Use master                      
   
   EXEC sp_helprotect '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
   
   
   
   -- Grant execute permissions to account
   
   GRANT EXECUTE ON xp_cmdshell TO [<Domain><NewUser>]
   
   

   
   

   
   


5. 
   

   Grant the person the uses the EXECUTE AS impersonate permission to the new SQL Server Login.

   
   
   

   
   

   GRANT IMPERSONATE ON LOGIN::[<Domain><NewUser>] TO [<Domain><UserGrantingExecuteAsUser>]
   
   

   
   

   
   


6. 
   

   Now run the command with the EXECUTE AS LOGIN and pass the net stop command to the xp_cmdshell stored procedure. The security principal as listed in step #5 above in the <Domain><UserGrantingExecuteAsUser> should be that of the person you are running SSMS as security context wise for this task.

   
   
   

   
   

   EXECUTE AS LOGIN = '<Domain><NewUser>'
   
   DECLARE @COMMAND nvarchar(4000)
   
   SET @COMMAND = 'net stop <servicename>'
   
   EXEC xp_cmdshell @COMMAND
   
   REVERT
   
   

   
   

   
   

Further Resources

• sp_xp_cmdshell_proxy_account


• CREATE USER (Transact-SQL)


• GRANT Server Principal Permissions (Transact-SQL)


• EXECUTE AS (Transact-SQL)