October 30, 2024 at 2:13 pm
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?
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply