Grant execute permissions for xp_cmdshell to a SQL Server Authenticated Account

_{Thursday, May 7, 2015 9:53 AM}

Hello All

I have recently migrated a set of SQL Server 7 databases to SQL Server 2005.  A few of the stored Procedures are using xp_cmdShell to transfer documents from one location to another using xp_cmdShell.  for the moment I've granted the SQL Server Login sysadmin Rights so that normal service remains unaffected; I want to grant the SQL Server Login permission to execute xp_cmdShell without granting it sysAdmin access.

Please click "Mark As Answer" if my post helped. Tony C.

_{Thursday, May 7, 2015 5:42 PM ✅Answered}

You would need to create the xp_cmdshell proxy account and grant EXECUTE permission to the non-sysadmin user that will execute xp_cmdshell. Please see the remarks section of xp_cmdshell for details (link below). For example:

EXEC sp_xp_cmdshell_proxy_account 'Domain\Name', 'Password';
go
GRANT EXECUTE ON [master].[dbo].[xp_cmdshell] to [myNonSysadminUser]
go

I would also like to strongly recommend considering an alternative solution. Instead of using xp_cmdshell, which can be subject to abuse and potentially to commend injections, you could create a CLR module that is scoped to perform the tasks that you need (transfer documents) and nothing else.

I hope this information helps.

Links:

Xp_cmdshell: https://msdn.microsoft.com/en-us/library/ms175046.aspx

sp_xp_cmdshell_proxy_account: https://msdn.microsoft.com/en-us/library/ms190359.aspx

-Raul Garcia

  SQL Server Security

This posting is provided "AS IS" with no warranties, and confers no rights.

_{Thursday, May 7, 2015 9:21 PM ✅Answered}

I agree with Raúl that a CLR solution with only performs the file transfer and nothing else is to prefer.

An alternative is to put the code that runs xp_cmdshell in a separate stored procedure. You sign that procedure with a certificate which exists in master. You create a login from that certificate and make that login member of sysadmin. That is not a login that can log in, it is just a place holder for permissions. With this solution, you don't need to grant any permissions to xp_cmdshell, but you may still have to create the proxy account. The advantage here is that xp_cmdshell is not wide open, but the stored procedure defines exactly what you can do with it.

For more inforamtion about certificate signing, see this article on my web site where I discuss this technique in detail:
http://www.sommarskog.se/grantperm.html

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Thursday, May 7, 2015 10:11 AM}

Hello Tony,

See xp_cmdshell (Transact-SQL) => Permissions "...When it is called by a user that is not a member of the sysadmin fixed server role..." for how to grant permissions.

Olaf Helper

[ Blog] [ Xing] [ MVP]

_{Thursday, May 7, 2015 10:28 AM}

Thanks Olaf

So if I'm understanding the topic correctly; I need to create a proxy account using: -

EXEC

sp_xp_cmdshell_proxy_account 'Domain\Name, 'Password';

GO

And the SQL Server login will automatically use this proxy acount?

Please click "Mark As Answer" if my post helped. Tony C.