How to grant access right to check SQL Agent Jobs ?

TonyJK 876 Reputation points
2022-08-26T02:45:36.803+00:00

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 ?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,910 questions
0 comments No comments
{count} votes

Accepted answer
  1. Olaf Helper 41,411 Reputation points
    2022-08-26T06:10:29.687+00:00

    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.


1 additional answer

Sort by: Most helpful
  1. YufeiShao-msft 7,061 Reputation points
    2022-08-26T06:19:34.817+00:00

    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.

    0 comments No comments