I've encountered another issue with SQL Server 2022 Contained Always On, so I wanted to inform the community.
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. The UI does not display SQL Agent service, so the user has to query msdb within group and view the catalog of jobs, and then execute the job through T-SQL commands.
Is this a bug? Am I missing something here?
I've tried adding the non sys admin user into the following roles: 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 ;
Still this user can't see anything within the UI.
Unless I escalate the user to a sys admin role.
