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.
 
xp_cmdshell
 
To grant a non-sysadmin user the permission to run xp_cmdshell
 
1) Add the user to the master database.
use master
GO
sp_grantdbaccess @loginame =’login
GO
 
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.
 
 
Advertisements
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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s