SQL Agent proxy account permissions in SQL 2005

Question

SQL Agent proxy account permissions in SQL 2005

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 1

It has to have a minimum of DTSOperator rights in msdb. And of course, whatever rights it needs to do what you're asking it to do. But DTSOperator is what it needs to run as a proxy.

Hope this helps.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

larry_a_wilson Old Hand Points: 325 More actions · Answer 2

I tried that and it still does not work. However, I am not running an SSIS package (which the db_dts... roles in msdb are generally for) in the job step. I am running a CmdExec step, trying to execute a C# program.

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 3

Did you give that acct specific rights to that type of action?

Check under sql server agent\proxies...

Maybe you're missing an OS perm... have you run this same thing from the OS as that particular user to see if it will go?

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

larry_a_wilson Old Hand Points: 325 More actions · Answer 4

Yes, I have been able to run the same program from the command prompt logged in as the same user.

Chris-215489 SSC Enthusiast Points: 134 More actions · Answer 5

This should get you working. This is in BOL

--xp_cmdshell Proxy Account

--When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to --Windows by using the account name and password stored in the credential named --##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

--The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this --stored procedure takes a Windows user name and password. For example, the following command creates a proxy --credential for Windows domain user SHIPPING\KobeR that has the Windows password sdfh%dkc93vcMt0.

-- Use this only in SQL Server 2005

sp_xp_cmdshell_proxy_account [ NULL | { 'account_name' , 'password' } ]

EdVassie SSC Guru Points: 60540 More actions · Answer 6

Larry,

The FineBuild Reference manual I put on Codeplex has some useful advice on creating SQL Agent and xp_cmdshell proxy accounts, and the authorities needed by the SQL service accounts, that may be of help to you.

Regarding Windows permissions, you only need 'Log on as a batch job'. Anything else just gives elevated privileges tha are not needed.

My experience is that a missing file permission is the normal cause of this type of problem. You certainly need to try running exactly the same script interactively that you have in the SQL Agent job, while logged on using your proxy account.

One debugging tip when logged on with the proxy account is to open a Command window, navigate to each folder your script needs to use and run a DIR command. Do not forget program libraries and Temp folders. If this all works, then run the DIR commands in your Agent job. Eventually the missing permission gets found.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara