How to Provide execute privileges to the SQL Account on XP_CMDSHELL procedure
xp_cmdshell procedure is an extended stored procedure of the system which cannot be directly called by the SQL authenticated user. This stored procedure can perform work on the areas where SQL Server directly cannot get involved.
You can actually perform the windows operation using the command like.
1. Copy/Move/Create/Delete files on the disk
2. Create/Modify/Exec the Windows level jobs and other application related tasks
Above two are just some sort of examples. You can actually write your code to implement any kind of windows operation from SQL Server using this stored procedure.
As this SP is doing windows work, only WINDOWS Authenticated user can perform this operation who has access to the server on which SQL Server is installed. This kind of access is usually restricted at DBA level and application teams do face some issues when trying to use this functionality.
We have to perform the below operations so that SQL Authenticated user can access the XP_CMDSHELL extended stored procedure.
USE master
GO
/*-- To allow advanced options to be changed.*/
EXEC sp_configure 'show advanced options', 1
GO
/*-- To update the currently configured value for advanced options.*/
RECONFIGURE
GO
/*-- To enable the feature.*/
EXEC sp_configure 'xp_cmdshell', 1
GO
/*-- To update the currently configured value for this feature.*/
RECONFIGURE
GO
EXEC sp_xp_cmdshell_proxy_account 'domainname\User','UserPwd';
/*--Above domain account should be existing in the sql server and has required access (controlserver permission)*/
Go
Create user [sql_authenticated_login_name] for [sql_authenticated_login_name];
/*--SQL Login which needs to access xp_cmdshell should have user in master database*/
GO
Grant EXEC on XP_CMDSHELL to [sql_authenticated_login_name];
Go
GO
/*-- To allow advanced options to be changed.*/
EXEC sp_configure 'show advanced options', 1
GO
/*-- To update the currently configured value for advanced options.*/
RECONFIGURE
GO
/*-- To enable the feature.*/
EXEC sp_configure 'xp_cmdshell', 1
GO
/*-- To update the currently configured value for this feature.*/
RECONFIGURE
GO
EXEC sp_xp_cmdshell_proxy_account 'domainname\User','UserPwd';
/*--Above domain account should be existing in the sql server and has required access (controlserver permission)*/
Go
Create user [sql_authenticated_login_name] for [sql_authenticated_login_name];
/*--SQL Login which needs to access xp_cmdshell should have user in master database*/
GO
Grant EXEC on XP_CMDSHELL to [sql_authenticated_login_name];
Go
