Access to SQL Server Agent and msdb

Question

I believe that SQL Server Agent and access to SQL Server Agent is a direct result to access to System Database [msdb]...if that's correct.

We use Windows Authentication SQL Server Access using Active Directory Groups.

The user in question has access to a specific Active Directory Group with inherited permissions to another Group which is the User defined within SQL Server and the Server in question and it most certainly does have access to [msdb] yet he still does not have access to SQL Server Agent when he connects to the Server via SQL Server Management Studio.

What am I missing here???

AD Group ===> _ACCESS-Server-TestServerName-Admin

• Inherited Group ===> _ROLE-IT-DB-Admin - Defined as Group Type "Security"
• SQL Server User ===> _ROLE-IT-DB-Admin and User Mapping for [msdb] has full access

Any help and/or insight would be GREATLY appreciated.

Thanks!

Answer 1

See:

https://learn.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles?view=sql-server-ver16

Answer 2

HI @Bobby P ,

The msdb database is just used by SQL Server Agent for scheduling alerts, msdb is available to any user with public level access to the instance, msdb database has fixed database roles to access to SQL Server Agent

To add a user as a member of these roles, for example:

use msdb  
EXECUTE sp_addrolemember  
@rolename = 'SQLAgentReaderRole',  
@membername = 'username'  

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

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.

Answer 3

First of all....Thanks for your quality feedback.

We did try this...And still No Luck...There has to be some logical reason as to why this user with AD groups and AD inherited groups cannot access SQL Server Agent.

USE [msdb]
;
EXECUTE [sp_addrolemember] @rolename = 'SQLAgentReaderRole',
@membername = 'WE_ROLE-IT-DB-Admin'
;

Answer 4

Interestingly enough, when we did the same EXEC to the specific User, he now has access to SQL Server Agent...which kind of defeats the purpose of AD Groups and Role based Security...There must be something hokie with the Group...But what???

So this worked...He now has access to SQL Server Agent...But we don't want to do it this way obviously...Hopefully just a temporary fix until I get a rock solid reply from hopefully a SQL Server Security expert that can maybe explain this whole mess better.

This works!

USE [msdb]
;
EXECUTE [sp_addrolemember] @rolename = 'SQLAgentReaderRole',
@membername = 'UserName'
;

Answer 5

I don't have the best of test environments, since I'm at home I don't have any AD.

But on my machine I have a test user SirPaul. I started SSMS with runas:

   runas /user:SirPaul SSMS  

I connected to an instance running SQL 2016 where I created a login for SirPaul. I noted that I did not see SQL Server Agent in Object Explorer. I closed SSMS.

From Computer Management I created a group Beatles and added SirPaul as member.

Then I ran from my regular SSMS window:

   USE msdb  
   go  
   CREATE USER  [SOMMERWALD\Beatles]  
   ALTER ROLE SQLAgentReaderRole ADD MEMBER [SOMMERWALD\Beatles]  

(SOMMERWALD is the name of my machine.)

I started SSMS as SirPaul again. This time I was able to see SSMS and also create a new job. (Which scarily enough you can do with a role called Reader!)

Exactly why it did not work for, I cannot say. I can't imagine that there is a difference between a local computer group and a group from a real AD. (And nor should it matter that you are using the older sp_addrolemember, I think.)