How to create SQL Agent Proxy Accounts in SQL 2005/2008 Server

In SQL Server 2005 and 2008, to allow a non-sysadmin login to execute xp_cmdshell, you will need to create a special system credential ##xp_cmdshell_proxy_account## by running the extended stored procedure sp_xp_cmdshell_proxy_account and specify a Windows account. This account will be used to run xp_cmdshell by users that are not members of the sysadmin role.

Step-1: Execute the following query from sql management Studio login as sa.

USE master 
GO

– Create a user that will be used for running xp_cmdshell called cmdshelluser
CREATE LOGIN cmdshelluser WITH PASSWORD=’passcmdshell’

– Add a Windows domain account Domain\User as the SQL Agent Proxy account
EXEC sp_xp_cmdshell_proxy_account ‘domain\sql2005proxy’, ‘password’;

– Grant database access to the SQL Server login account that you want to provide access.
EXEC sp_grantdbaccess ‘cmdshelluser’

– Grant execute permission on xp_cmdshell to the SQL Server login account. 
GRANT exec ON sys.xp_cmdshell TO cmdshelluser 
GO

Step-2: Verify that the account is created.
–To confirm that the ##xp_cmdshell_proxy_account## credential has been created.
select * from sys.credentials

Step-3: Test executing the xp_cmdshell sp.
EXECUTE AS login = ‘cmdshelluser’
–Execution of xp_cmdshell is allowed.
–And executes successfully!!!
EXEC xp_cmdshell ‘DIR C:\*.*’

Now we are going to explore more on xp_cmdshell with bcp utility. We will use xp_cmdshell and bcp utility to export database table data to remote pc as .csv file in a domain environment. Just follow the steps described below:

Step-4: Grand data reader access to cmdshelluser to the database from where you want to export table data.

USE Dbname
GO
EXEC sp_grantdbaccess ‘cmdshelluser’
GO
EXEC sp_addrolemember N’db_datareader’, N’cmdshelluser’
GO

Step-5: Create one shared folder on remote computer in the same domain.From sharing Tab, allow everyone group change permission and from security tab, grant domainUsers Group modify Permission.

Step-6:  Now time to test.
declare @cmd nvarchar(1000)
set @cmd = ‘bcp “select * from db_name..table_name” queryout \\remoteserver\CSVReports\test.csv -U cmdshelluser -P passcmdshell -S”dbserver\SQL2005ENT”  -w’

EXECUTE AS login = ‘cmdshelluser’
exec master..xp_cmdshell @cmd

If everything is ok, it will export table data to remote server.

 

In SQL Server 2005 and 2008, to allow a non-sysadmin login to execute xp_cmdshell, you will need to create a special system credential ##xp_cmdshell_proxy_account## by running the extended stored procedure sp_xp_cmdshell_proxy_account and specify a Windows account. This account will be used to run xp_cmdshell by users that are not members of the sysadmin role.

Step-1: Execute the following query from sql management Studio login as sa.

USE master 

GO

– Create a user that will be used for running xp_cmdshell called cmdshelluser

CREATE LOGIN cmdshelluser WITH PASSWORD=’passcmdshell’

– Add a Windows domain account Domain\User as the SQL Agent Proxy account

EXEC sp_xp_cmdshell_proxy_account ‘domain\sql2005proxy’, ‘password’;

– Grant database access to the SQL Server login account that you want to provide access.

EXEC sp_grantdbaccess ‘cmdshelluser’

– Grant execute permission on xp_cmdshell to the SQL Server login account. 

GRANT exec ON sys.xp_cmdshell TO cmdshelluser 

GO

Step-2: Verify that the account is created.

–To confirm that the ##xp_cmdshell_proxy_account## credential has been created.

select * from sys.credentials

Step-3: Test executing the xp_cmdshell sp.

EXECUTE AS login = ‘cmdshelluser’

–Execution of xp_cmdshell is allowed.

–And executes successfully!!!

EXEC xp_cmdshell ‘DIR C:\*.*’

Now we are going to explore more on xp_cmdshell with bcp utility. We will use xp_cmdshell and bcp utility to export database table data to remote pc as .csv file in a domain environment. Just follow the steps described below:

Step-4: Grand data reader access to cmdshelluser to the database from where you want to export table data.

USE Dbname

GO

EXEC sp_grantdbaccess ‘cmdshelluser’

GO

EXEC sp_addrolemember N’db_datareader’, N’cmdshelluser’

GO

Step-5: Create one shared folder on remote computer in the same domain.From sharing Tab, allow everyone group change permission and from security tab, grant domainUsers Group modify Permission.

Step-6:  Now time to test.

declare @cmd nvarchar(1000)

set @cmd = ‘bcp “select * from db_name..table_name” queryout \\remoteserver\CSVReports\test.csv -U cmdshelluser -P passcmdshell -S”dbserver\SQL2005ENT”  -w’

EXECUTE AS login = ‘cmdshelluser’

exec master..xp_cmdshell @cmd

If everything is ok, it will export table data to remote server.