January 20, 2009 at 9:23 am
Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed
User who has dtsadmin,sqlagent operator role getting this error in sql 2005 agent.
Is there anything else i need to setup so that the user can exec his job?
January 20, 2009 at 10:26 am
The db_dtsadmin role is not applicable to DTS packages. Regardless of whether the job runs a DTS package or an SSIS package, you must create a security credential and a SQL Agent proxy for a non-sysadmin to execute a job.
Greg
January 20, 2009 at 12:16 pm
how do i do that?
January 20, 2009 at 12:19 pm
In management studio, if you open up SQL Server Agent on your server, there's a folder for Proxies. Right click that and select New. Follow the directions. That should get you what you need. If not, check Books Online, look up Proxies, and it will give you directions on how to fix the problem.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 20, 2009 at 12:25 pm
Instead of doing this can i just change owner of the job to my login (sysadmin) and if i do it can they still run the job and also make changes to the job when required if given " sqlagentoperator role"
January 20, 2009 at 12:42 pm
No. They'll be able to start the job but it will still fail on the package step. You need a proxy that has been configured to use the Integration Services Package subsytem so it can run a job that executes a package.
Greg
January 20, 2009 at 12:53 pm
What will be the windows id that is associated with proxy, can that be sysadmin or sql service account.
January 20, 2009 at 1:03 pm
It will be whichever non-admin login you want to run the job. If you click on the "Add.." button you'll see your choices are logins, server roles, or msdb database roles.
Greg
January 20, 2009 at 1:30 pm
But if i want to create a proxy for that non admin account, how wud i create a credential for that id unless i know that account pswd.
January 22, 2009 at 11:34 am
My mistake. I should have said, "whichever admin login you want to run the job. When I've created a credential, I've used the login which runs SQL Server Agent. And yes, you do need to know the password for it.
Greg
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply