Post reply

System views in a contained availability group

  • October 30, 2024 at 2:13 pm

    #4475432

    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?

     

  • October 31, 2024 at 12:34 pm

    #4475894

    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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply