System views in a contained availability group
-
I have a query that runs in a job to check on orphaned users. On a server with a contained availability group it gives false positives. I pinned it down to the following different result of the same query.
When I am in SSMS and connected to one of the servers in the AG. I do a right-mouse-click on the server I start a new query. I run the query:
USE [AG1_master]
SELECT * FROM [AG1_master].[sys].[server_principals];
I get the logins that are at instance level. So concluding I think the result is still from [master].[sys].[server_principals]
When I do a right-mouse-click on the AG1_master and start a new query, and run the same query, I get the logins that are at the AG level. So I think it is really looking at the [AG1_master].[sys].[server_principals] table. In the checking job I want both results but I always getting them only from the [master].[sys].[server_principals] table. How can I skip the step that SQL Server first go to the master database?
-
So you connect to one of the nodes of the Windows cluster hosting the Contained AG ?
( I don't know if access to master db is blocked when connecting to the listener )
Did you try this:
/* contained AG master db only available when connected using the listener ! */
SELECT 'AG1_master' SourceMaster, * FROM [AG1_master].[sys].[server_principals]
/* regular master db only available when not connected using the listener !*/
SELECT 'master' SourceMaster, * FROM [master].[sys].[server_principals];-edited-
Have a look at "Connect to a Contained Availability Group" in sqlha "contained-availability-groups-in-sql-server-2022"
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Both databases are reachable through both logging onto the server as logging on to the listener. When I log on through the listener I get the same results as doing a right-mouse-click new query on the AG1_master database. Looks like if you do a right-mouse-click new query on the AG1_master you simulating logging on to the listener.
-
Did you actually prefix the correct master database ?
In that case you might as well perform a "union all"
/* contained AG master db */
SELECT 'AG1_master' SourceMaster, * FROM [AG1_master].[sys].[server_principals]
union all
/* regular master db */
SELECT 'master' SourceMaster, * FROM [master].[sys].[server_principals];Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Did some extra digging:
When I log on to the listener and I start a new query in SSMS. Column contained_availability_group_id in sys.dm_exec_sessions is filled with a GUID.
When I log on to the primary node and start a query through right-mouse-click/new-query on the servername, column contained_availability_group_id in sys.dm_exec_sessions is NULL.
When I log on to the primary node and start a query through right-mouse-click/new-query on AG1_master, column contained_availability_group_id in sys.dm_exec_sessions is filled with a GUID. So this simulates login in on the listener.
But how can I do that from logging in from another server using OPENQUERY?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply