Net stop <service> system error 5 access denied

Question

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?

Answer 1

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

Answer 2

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)