SQL Server Agent Job
-
Hi,
I have two databases in sql 2008r2 for which i will use to populate one database from another.
I have created a sql server agent job but this job keeps failing.The job is going to be executing a TSQL command.
Please could someone explain how this should be set up.ie do i need to create a user to execute the job.
When i run the job it fails saying i do not have the correct permissions for accessing the database.
Please could someone point me in the right direction for the steps needed to create a job with user permissions etc.As this is now driving me nuts.
Thank you for your help
J
-
79J (12/21/2011)
Hi,I have two databases in sql 2008r2 for which i will use to populate one database from another.
I have created a sql server agent job but this job keeps failing.The job is going to be executing a TSQL command.
Please could someone explain how this should be set up.ie do i need to create a user to execute the job.
When i run the job it fails saying i do not have the correct permissions for accessing the database.
Please could someone point me in the right direction for the steps needed to create a job with user permissions etc.As this is now driving me nuts.
Thank you for your help
J
The Job is executed by the account used to run the SQL Server Agent Service. If that account doesn't have enough permissions to execute the T-SQL then the Job will probably fail. I'm not sure I understood what you are trying to do with the Job, but if you want you can try giving the account the permissions it needs to execute the T-SQL.
-
You should be a sysadmin to run sql agent job or you can assign this role
SQLAgentOperatorRole :members have additional permissions on local jobs and schedules. They can execute, stop, or start all local jobs, and they can delete the job history for any local job on the server. They can also enable or disable all local jobs and schedules on the server. To enable or disable local jobs or schedules, members of this role must use the stored procedures sp_update_job and sp_update_schedule. Only the parameters that specify the job or schedule name or identifier and the @enabled parameter can be specified by members of SQLAgentOperatorRole. If they specify any other parameters, execution of these stored procedures fails. SQLAgentOperatorRole members cannot change job ownership to gain access to jobs that they do not already own.
-
Ignacio A. Salom Rangel (12/21/2011)
79J (12/21/2011)
Hi,I have two databases in sql 2008r2 for which i will use to populate one database from another.
I have created a sql server agent job but this job keeps failing.The job is going to be executing a TSQL command.
Please could someone explain how this should be set up.ie do i need to create a user to execute the job.
When i run the job it fails saying i do not have the correct permissions for accessing the database.
Please could someone point me in the right direction for the steps needed to create a job with user permissions etc.As this is now driving me nuts.
Thank you for your help
J
The Job is executed by the account used to run the SQL Server Agent Service. If that account doesn't have enough permissions to execute the T-SQL then the Job will probably fail. I'm not sure I understood what you are trying to do with the Job, but if you want you can try giving the account the permissions it needs to execute the T-SQL.
Another option - if you don't want the Agent account to have too many priviliges - is to execute the job using a proxy:
http://msdn.microsoft.com/en-us/library/ms189064.aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply