Orphaned User Detection for SQL Server High Availability Databases
One of the issues with Availability Groups is that you need to make sure the SIDs on the primary and secondary are the same, so when you failover there are no issues connecting to the database. You may be unsure if the user SIDs match between the primary and secondary high availability databases, so when a failover occurs it may mean you have orphans and the logins will not have access to the databases. In this tip we will look at how to find if there are issues and how to resolve.
Use this process to compare the SIDs on your primary and secondary SQL Server instances to find if there are issues and also resolve the issues prior to failover and finding out there is a problem.
This solution uses the sp_MSforeachdb system stored procedure in the master database. If an instance does not have the stored procedure then script it out from an existing instance and copy it over. There is also a better for each database stored procedure in this tip 2201, though this is very handy, it will not work for what we need due to nested inserts.
You are following these instructions at your own risk, so be careful and also make sure you know what you are updating and why prior to making updates on your production servers.
Determine Instances Hosting Availability Groups
Run this T-SQL on your primary High Availability instance to find the list of instances that are participating.
select distinct replica_server_name from sys.availability_replicas
This will list the primary and replica instances. For our example, we have HA_PRIMARY and HA_DR.
Create Linked Server
On the HA_PRIMARY server we will create a linked server to the HA_DR instance. This will let us run a query against both instances to check for any differences. This should be done for all instances that are part of the Availability Group.
When creating the linked server, create it with enough authority so that you can interrogate the users. You can create a specific security account and grant an account the security admin role on the failover instance with something like the following code.
ALTER SERVER ROLE [securityadmin] ADD MEMBER [YOUR_SECURITY_ACCOUNT_RW]
This would be the server roles to have enough access to get the information needed.
You could right click on Linked Servers to create a new linked server.
List Databases Enrolled in Availability Groups
Connect to the primary instance and run the following code. This code will show the databases enrolled in Availability Groups on the instance you are connected to.
The list of databases returned are the ones we need to investigate.
-- Get databases from the instance I am connected to Select name from sys.databases Where name in ( -- Where the database is enrolled in High Availability select distinct states.database_name from sys.dm_hadr_database_replica_cluster_states states left join sys.availability_replicas replicas on states.replica_id=replicas.replica_id ) and is_in_standby=0 -- And the database is not in standby mode and state_desc='ONLINE' -- And the database is online
List Users in Availability Group Databases on Primary
To take this further, the following code will return a list of all database users for the databases listed above. We are going to ignore these SQL users: ('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA'). You can add more users at the end of the query if you want to exclude others.
BEGIN -- Create a temporary store for the data IF EXISTS (SELECT TOP 1 name FROM tempdb.sys.objects (nolock) WHERE name LIKE '##HA_DB_Users_Primary%') DROP TABLE ##HA_DB_Users_Primary CREATE TABLE ##HA_DB_Users_Primary ( [Primary Server] nvarchar(1024) null, [DBName] SYSNAME null, [Name] SYSNAME null, [default_database_name] SYSNAME NULL, [Primary sid] VARBINARY(85) ) -- Collect the SQL Server authenticated users INSERT INTO ##HA_DB_Users_Primary EXEC master.dbo.sp_MSforeachdb 'SELECT distinct @@SERVERNAME,''?'', u.name, l.default_database_name, u.sid FROM [?].sys.database_principals u LEFT JOIN ([?].sys.database_role_members m JOIN [?].sys.database_principals r ON m.role_principal_id = r.principal_id) ON m.member_principal_id = u.principal_id LEFT JOIN [?].sys.server_principals l ON u.sid = l.sid WHERE u.TYPE =''S'' and ''?'' in ( Select name from sys.databases Where name in ( select distinct states.database_name from sys.dm_hadr_database_replica_cluster_states states left join sys.availability_replicas replicas on states.replica_id=replicas.replica_id ) and is_in_standby=0 and state_desc=''ONLINE'' ) and u.name not in (''dbo'',''guest'',''sys'',''INFORMATION_SCHEMA'') order by u.name ' END
List Users in Availability Group Databases on Replica
This script gets the Users from the replica via the linked server which we will use to compare the results from both servers.
-- Check we have a link to the HA replica instance Declare @replica_server_name nvarchar(1024) select top 1 @replica_server_name = name FROM sys.servers where name in (Select distinct replica_server_name from sys.availability_replicas where failover_mode=0) -- Collect data from DR BEGIN -- Create a store for the data IF EXISTS (SELECT TOP 1 name FROM tempdb.sys.objects (nolock) WHERE name LIKE '##HA_DB_Users_DR%') DROP TABLE ##HA_DB_Users_DR CREATE TABLE ##HA_DB_Users_DR ( [Name] SYSNAME NULL, [DR sid] VARBINARY(85) null, [create_date] datetime2 null ) -- Collect the SQL Server authenticated users Declare @SQL_String nvarchar(max) Set @SQL_String='INSERT INTO ##HA_DB_Users_DR SELECT name,sid, create_date FROM [' + @replica_server_name +'].master.sys.server_principals where type_desc=''SQL_LOGIN'' and name is not null' exec (@SQL_String) END
Compare Users from Primary and Replica
This query produces a list of results. Notice we compare the SIDs.
-- Report results SELECT [Primary Server], DBName as [Primary Database], isnull(J001.[Name],'====> Missing User <====') as [Primary Name], isnull(J002.Name,'====> Missing User <====') as [DR Name], [Primary sid], [DR sid], case when [Primary sid] <> [DR sid] then 'Orphan' else '' end as [Orphan check] , @replica_server_name as 'DR Instance' FROM ##HA_DB_Users_Primary J001 left join ##HA_DB_Users_DR J002 on J001.[Name] = J002.[Name]
To get the results, we need to run all of the scripts in the same query window.
After running the above scipts, below we can see an orphan, the user SID on the primary does not match the user SID on the DR site.
If the account did not exist on the secondary site we would have a NULL instead of the SID as follows:
Three Potential Problems
This article will consider three error types.
Accounts not at the DR site
- Use tip 3650 by K. Brian Kelley as it shows you how to copy logins with the right SID.
Accounts not at the primary site
- Use tip 3650 by K. Brian Kelley as it shows you how to copy logins with the right SID.
Accounts on both sides, but different SIDs
- Follow the solution below.
Steps to Fix Mismatched SIDs
Connect to the primary site and script out the login with encrypted password and SID using the sp_help_revlogin stored procedure from Microsoft as follows:
EXEC master.dbo.sp_help_revlogin 'Orphan_Test'
It will give something like this: (note: I shortened the SID values for the demo)
-- Login: Orphan_Test CREATE LOGIN [Orphan_Test] WITH PASSWORD = 0x0200BF65C88B8090D3ED2 HASHED, SID = 0x5AA1D60278D9424, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
Connect to the DR site and confirm the user account is only used by the HA databases you are working with. If it is used by other databases, you will need to make sure you don't make a change that could impact other databases.
Open the account that has the wrong SID on the secondary. Note that you should get this warning “One or more databases are inaccessible and will not be displayed in list.”, because your HA database should not be accessible on the secondary site.
Note: if you do not get this error you might still be connected to the primary site and you would be deleting an active account, so make sure you are on the right server.
If the SQL account is only used by your HA databases and has the wrong SID then delete the SQL login on the secondary and apply the script that you generated previously with sp_help_revlogin to create the login with the correct SID.
Rerun the scripts on the primary to make sure SIDs now match.
When possible failover the database and check that the account now works.
When scanning multiple instances in your environment, it would be wise to build some check tables to make sure you don't make mistakes.
|Instance A||Instance B||Instance C|
|Run orphan check script||(List things to fix)|
|Remove orphan accounts from replica instances|
|Run orphan check script again|
- NOTE: Tip 3650 by K. Brian Kelley will help automate the correct creation of accounts in DR.
- NOTE: Tip 2624 by Dale Kelly will not work on databases in High Availability.
- NOTE: Tip 3439 by Percy Reyes will not work on databases in High Availability.
- NOTE: Tip 1590 by Atif Shehzad explains the basics of how orphans are created in a non-HA environment.
- NOTE: Tip 1864 by Tim Ford is using the term Orphans in a different way than we are in this article.
- Read: Tip 3650 by K. Brian Kelley will help automate the correct creation of accounts in DR.
- Read: Tip 1590 by Atif Shehzad explains the basics of how orphans are created in a non-HA environment.
- Read: Microsoft Troubleshoot Orphaned Users.
Last Updated: 2018-03-15
About the author
View all my tips