Handling xp_cmdshell in SQL Server 2005

The security model of SQL Server 2005 has been modified alot from its previous version SQL Server 2000. xp_cmdshell had few security flaws that are removed in this version.

The modification are -
1. xp_cmdshell can only be granted to a login with sysadmin permission.
2. If xp_cmdshell is granted to a login without sysadmin role, xp_cmdshell proxy must be configured before the login can run
xp_cmdshell.


Let's understand the same with a scenario -

Scenario:
Login login_01 has to granted execute permission on xp_cmdshell. login_01 does not have sysadmin server role granted and also it does not have access to master database.

Solution:
Before performing any change, run sp_helplogins to this login. This would give you info whether the login has permission to master database or not.

If the login is lacking this permission, run the below statement -

Use master
GO
exec sp_grantdbaccess 'login_01' , 'login_01'
Grant exec on xp_cmdshell to [login_01]

Now, change the security context to the login_01 and run xp_cmdshell. Something like this -

execute as login='login_01'
GO
exec xp_cmdshell 'd:\apps'
GO

If your server is missing proxy to xp_cmdshell, it would fail with error. Now revert the context to your login and then, create the proxy. Before proceeding, decide which Windows Login has to be used as xp_cmdshell Proxy. Say, the login name is
Domain\appsvc.

EXEC sp_xp_cmdshell_proxy_account 'Domain\appsvc','sdfh%dkc93vcMt0'
GO

Note: You can create one proxy only for xp_cmdshell. Already available xp_cmdshell proxy would get replaced by running the proxy statement.

Few more points to remember -
1. No mapping is required between xp_cmdshell_proxy and login.
2. Proxy login need not be granted SQL Server level permissions.