How to grant access right to check SQL Agent Jobs ?

Question

Hi,

There is a request by consultant to grant RDP access right to Database Server.

He mentions that he need to find out why jobs failed.

We prefer to give me db_owner to the those databases for his application. However, he argues that checking SQL Agent Jobs need sysadmin right.

Is there any suggestion ?

Answer 1

to grant RDP access right to Database Server.

RDP is a Windows feature and has nothing to do with SQL Server.

We prefer to give me db_owner to the those databases

Jobs are not really related to single databases, dbo permissions won't work.

he argues that checking SQL Agent Jobs need sysadmin right.

Absolutly not, see https://learn.microsoft.com/en-us/sql/ssms/agent/configure-a-user-to-create-and-manage-sql-server-agent-jobs?view=sql-server-ver16

There is a request by consultant

By that request I would say he is not the best in his job; but that's only my private opinion.

Answer 2

Hi @TonyJK ,

If you want to have the permission to see SQL Agent jobs, you do not need to grant full administrator privileges.

There are some msdb database fixed database roles which can access to SQL Server agent:
SQLAgentUserRole

SQLAgentReaderRole

SQLAgentOperatorRole

Please check out this doc:
SQL Server Agent Fixed Database Roles

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.