SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,692 questions
SQL Server 2022 Contained Always On SQL Agent UI display issues for non sys admin roles
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 12%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
SQLDan
0
Reputation points
Hello to all, I have a lab set up within my environment, It is currently running SQL Server 2022 CU3 with a 4 node setup under Always On Contained Groups.
Within this I have a test database , and I have master / msdb with the option as reuse system database when creating this contained group.
I've notice that only SysAdmins are allowed to view SQL Agent jobs within the group listeners, and just like any conventional SQL Agent tasks the UI is available to create new tasks, modify jobs,etc.
However, when you simply create a non sys admin user, but you want to allow a sql user to operate , change, edit, etc . The UI does not display any jobs.
The only option I see for the user is to use T-SQL to be able to run / edit / etc/ jobs.
Is this a bug? Am I missing something here?.
USE [AG_Con_w_ReuseSys_msdb]
create user sqlUser from login sqlUser
ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER sqlUser ;
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER sqlUser ;
ALTER ROLE [SQLAgentUserRole] ADD MEMBER sqlUser ;
The only time the user can see the agent jobs is if the user is a member of the sys admin role.
This particular user can log in within the Listener , but the user simply can't see any jobs using the UI.