March 25, 2008 at 5:59 am
My Sql server user will not be given sysadmin role in Production environment.in that case how the job will get execute??
Appriciate the Answer in Advance.
March 25, 2008 at 6:03 am
vijay (3/25/2008)
My Sql server user will not be given sysadmin role in Production environment.in that case how the job will get execute??Appriciate the Answer in Advance.
I'm not sure I understand your question.
A job will execute using the security credentials of the owner of that job - unless the job's owner is a sysadmin user, in which case it will use the credentials of the service account that runs SQL Server Agent.
March 25, 2008 at 6:57 am
karl,
Thanks for quick response!!!
My Prime Query is Currently in DEV Environment I have a User called "MyUser" which has Sysadmin Rights thus I have Created all the Jobs and its Executes Properlly.
But the same time when I move all the jobs to PROD Environment at that time "MyUser" dosnt hold Sysadmin rights. At that time how "MyUser" can Execute the Jobs?
Adding a Question on your solution(need to use Proxy for Service Agent). Where/Why we should use Proxy Account to Run a JOB ??? (I mean Creating Credentials then Proxy then attached the Proxy with Job)
Appriciated your Response.
March 25, 2008 at 7:07 am
So do you want MyUser to execute the jobs or do you just want to schedule them but you find that you can't because MyUser owns the job?
I personally avoid non-sa users from owning jobs. That is, I almost always use sa as the owner of the job.
March 25, 2008 at 7:15 am
Yes!! I Now have requirment where in I want to own as well as execute the Jobs thru "MyUser" which is now Non-Sysadmin.
March 25, 2008 at 7:58 am
vijay (3/25/2008)
Yes!! I Now have requirment where in I want to own as well as execute the Jobs thru "MyUser" which is now Non-Sysadmin.
Add the user to the TargetServerRole database role in the msdb database.
March 25, 2008 at 8:22 am
I did that but. once running the Job its blew up with following error:
Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.
March 25, 2008 at 8:55 am
vijay (3/25/2008)
I did that but. once running the Job its blew up with following error:Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.
Have you created the proxy account? If not you have to create a credential first:
CREATE CREDENTIAL my_cred WITH IDENTITY = 'MyUser'
And then you can create the proxy account by expanding SQL Server agent, right-clicking on Proxies and selecting New Proxy. Then just choose SSIS Package for the subsystem and add MYUser as a principal.
March 25, 2008 at 9:26 am
I Did the same. But once adding that Proxy as "Run as" Tab. and then saving the Job give me error-> Only member of sysadmin role or owner of DTS Job can create the new version of Job.
So here too its not working.Now What I am understanding from all the R & D that.
Creation/Updation of Job can only be Done By Member of SysAdmin Role. And this Correct then how i goona do the Job Setup in My PROD Environment??
March 25, 2008 at 9:55 am
I think you might need to add the user to some of the roles in the msdb database - in addition to the TargetServerRole. I can't quite remember now but I'm sure this was the case.
March 26, 2008 at 4:14 am
You need to look at SQL Agent Proxies. These allow non-sysadmin accounts to run SQL Agent jobs.
I suggest you download the SQLServerFineBuild Reference doc from Codeplex and look at the section on SQL Agent proxies. This says how to set them up, and gives recommendations on how to deal with the security issues.
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
March 26, 2008 at 4:34 am
Thanks for the info.But I will not be allowed to install any third party tool in PROD Environment.
March 26, 2008 at 6:07 am
You'r right!!
after doing some googling i got to know
When Mapping the MyUser to MSDB Database there we need to check following things
>db_ddladmin
>db_dtsadmin
>db_dtsoperator
and all SQLAgent roles.
Then this user will have rights to own/create/updated the job. and for this we need to have Proxy Account.
and things will work.
Note:adding Target serverrole along with above mentioned role then it doent work, so i omitted.
Appriciate for all your Help.. 🙂
March 26, 2008 at 7:34 am
Vijay, you don't need to install the Reference document, just read it...
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
March 26, 2008 at 7:52 am
EdVassie,
Appriciates your Help!!!
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply