RDS SQL Server Agent Roles

Hi Sanjeev Reddy,

Please go through below steps i hope it will helpful to resolve your issue.

SQLAgentUserRole:

• Members of this role can view and manage only their own jobs.
• They cannot view or manage jobs created by other users.
• This role is suitable for users who need to create and manage their own jobs but do not require access to jobs created by other users.

SQLAgentReaderRole:

• Members of this role can view all SQL Server Agent jobs, including those created by other users.
• They cannot create or manage jobs.
• This role is suitable for users who need to monitor the status of all jobs but do not need to manage them.

SQLAgentOperatorRole:

• Members of this role can view and manage all SQL Server Agent jobs.
• This role is similar to the SQLAgentUserRole but with the additional capability to manage jobs created by other users.
• It is suitable for users who need to have full control over all jobs.

Granting Permissions to Users

To allow users to execute their own jobs and also manage other jobs, you need to grant them the SQLAgentOperatorRole. Here’s how you can do it:

• Connect to your RDS SQL Server instance using a tool like SQL Server Management Studio (SSMS).

• Assign the SQLAgentOperatorRole to the user:

USE msdb;
EXEC sp_addrolemember 'SQLAgentOperatorRole', 'your_username';

Verify the Role Assignment:

USE msdb;
SELECT 
    u.name AS Username,
    r.name AS Role
FROM 
    sys.sysusers u
INNER JOIN 
    sys.database_role_members rm ON u.uid = rm.member_principal_id
INNER JOIN 
    sys.database_principals r ON rm.role_principal_id = r.principal_id
WHERE 
    r.name IN ('SQLAgentUserRole', 'SQLAgentReaderRole', 'SQLAgentOperatorRole');

Important Consideration:

AWS RDS Restrictions: RDS has certain limitations and differences compared to a full SQL Server instance. For example, the use of certain system stored procedures and features may be restricted or unavailable.

Monitoring and Auditing: Ensure you have appropriate monitoring and auditing in place to track the execution and status of SQL Server Agent jobs.

User Access Management: Regularly review and update user roles and permissions to maintain security and compliance with your organization’s policies.

Hi Ranga Swamy,

Thanks for your inputs, The RDS don't have SQLAgentReaderRole permission role and when never i will give the SQLAgentOperatorRole , After refresh the RDS SQL Server the permissions revoked. as per AWS, we need to remove the permission before disconnected from SQL Server. its not same as SQL Server .

Thank, Sanjeev K