2

I created a login and a user. I assigned the user as the db owner of the database "test". Then I granted him the role SQLAgentOperatorRole so that he can delete/create/modify the jobs belonging to the database "test". But I can only view the jobs though I have granted SQLAgentOperatorRole. What should I do to allow this user to do anything to the jobs belonging to the database "test" ? Please advice.

2

That is not part of the definition of SQLAgentOperatorRole permissions.

The login being an owner of database "test" does not influence how SQL Agent jobs are managed. Being a db_owner of the database means that a job with that owner can take almost any action within the "test" database, but it says little about operating the SQL Agent job itself

The SQLAgentOperatorRole permissions are describe in the following link. https://msdn.microsoft.com/en-us/library/ms188283.aspx

Look for the topic: The following table summarizes SQLAgentOperatorRole permissions on SQL Server Agent objects.

This clearly shows that the SQLAgentOperatorRole will allow the login to create/delete/modify SQL Agent jobs that are owned by the user's login. However, the login can be used to issue commands to enable/disable other SQL Agent jobs and job schedules.

2

According to the SQL Server documentation, a user with a SQLAgentOperatorRole can only modify jobs that it created itself.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.