Grant permissions on xp_cmdshell and sp_OACreate family SPs

The defualt permissions to run xp_cmdshell and sp_OACreate family system stored procedures are members of sysadmin server role. However, use a login with sysadmin privilge to run an application is risky, thus not a good practice.
If an application needs to run those system stored procedures without using logins of sysadmin, we can grant the execution permission on those proceudres to the login.
To grant a non-sysadmin user the permission to run xp_cmdshell
1) Add the user to the master database.
use master
sp_grantdbaccess @loginame =’login
2) Grant the execution permission to the user
GRANT EXECUTION ON xp_cmdshell TO the user
3) Define a proxy account for non-sysadmin user becuase xp_cmdshell impersonates the SQL Server Agent proxy account if the user is not a member of sysadmin role.
Go to Management–>SQL server agent–>Properties–>Job systems, uncheck the "Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps" check box, and assign a windows account in the pop up dialog box. Or run xp_sqlagent_proxy_account to specify the account.
For sp_OACreate family stored procedures, only the first two steps are necessary.
This entry was posted in SQL server. Bookmark the permalink.

One Response to Grant permissions on xp_cmdshell and sp_OACreate family SPs

  1. Youjin says:

    very informative! Peter!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s