Orphaned User Detection for SQL Server High Availability Databases

By: Graham Okely   |   Updated: 2018-03-15   |   Comments (2)   |   Related: > Availability Groups

Problem

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.

Solution

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.

In addition, this solution also uses the sp_help_revlogin and sp_hexadecimal stored procedures.

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] 
			

Example Output

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.

Tip

When scanning multiple instances in your environment, it would be wise to build some check tables to make sure you don't make mistakes.

Research

• 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.

Next Steps

• 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.

About the author

Graham Okely is a SQL Server DBA and has been working with database systems since 1984 and has been specializing in SQL Server since 2007.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips