This article gives an overview of Orphan users and fixing them using DBATools PowerShell.

Overview of Orphan Users

In every organization, we have multiple database environments such as Production, UAT, QA, Test, and Development. We do not have live data in an environment other than production. Usually, a DBA gets a request to refresh lower environments with the recent production database backup. It helps to validate any application release before deploying in production.

Once we take Production database backup and restore it into lower environments, we get the issue of Orphan users. You can also get this error in moving the database with Detach and Attach method also. Sometimes you can database online, but application users cannot access it.

In my earlier articles SQL Database Backups using PowerShell Module – DBATools and SQL Restore Database using DBATools, we explored SQL database backup and restored using open-source DBATools PowerShell.

Recently I restored database from SQL instance ( .\SQlExpress) to another SQL instance (.\SQL2019CTP) . Once the application users try to access the database, it gave the following error.

Let’s troubleshoot it further. In the following screenshot, you can see a connection to destination SQL instance (.\SQL2019CTP). It shows that SQLDB login exists there.

Right click on login and provide db_datareader role in SQLDB database.

Click Ok, and you get another error. We get error 15023 that means user already exists in the SQLDB database, but still we cannot provide any permission to the user to access this database.

We get the errors due to the Orphan users issue in our database environment. We will look at resolving it with DBATools PowerShell. First, we will get a quick overview of Orphan users.

In SQL Server, a database user is linked to a SQL Server login. A database user and SQL Server logins link with each other using SID’s.

We might issue with logins in the following scenarios.

• Login does not exist in the instance
• Login exists, but there is a mismatch in database user SID and SQL Server login SID

In SQL Server, we can check the Orphan user in a database with stored procedure sp_change_users_login. Execute this query in our database, and we can see Orphan user.

Fix Orphan users using DBATools PowerShell module

DBATools is a powerful PowerShell Module that contains useful functions to do administrative tasks with minimum efforts. We can check all commands related to a particular keyword in DBATools PowerShell using Get-help command.

• Note: In this article, I am using an integrated terminal of Azure Data Studio to execute DBATools command. You can also use the Windows PowerShell console to execute these as well.

Let’s check command related to keyword Orphan.

Once we restore SQL database in another instance, we can get a list of orphan users with Get-DbaDbOrphanUser command. We should always check the syntax description of a particular command before using it.

The syntax of Get-DbaDbOrphanUser in DBATools PowerShell

Let’s execute this command in my earlier example. In the following command, it checks orphan users in all online databases in SQL instance.

In the output, we can see that we have orphan user SQLDB in the database SQLDB.

Suppose we want to check Orphan users in a specified database only. We can execute command Get-DbaDbOrphanUser on a specified database with -Database parameter.

We can use another DBATools PowerShell function Repair-DbaDbOrphanUser to fix Orphan users in SQL Server.

Repair-DbaDbOrphanUser command in DBATools PowerShell

It helps to find Orphan users and fix them with remap of their SID’s.

We can use the following query to fix all orphan users in SQL instance.

Once we run this command, it remaps the SID’s and fixes the orphan users.

We can run Get-DbaDbOrphanUser to check if an orphan user still exists in SQLDB or not. We do not get any records this time. It shows we do not have any Orphan users in the SQLDB.

We can verify it again with system stored procedure sp_change_users_login. We do not get any records for orphan users.

We can see a few more use cases of Repair-DbaDbOrphanUser.

Fix Orphan user for a specific database

Fix Orphan user for multiple databases

Fix Orphan users for a specific database and specific user

Now, right click on the SQLDB user and go to properties. Previously, we do not see any mapping of SQLDB users in this database. It does not show any existing permissions in SQLDB database. We could not modify permissions as well.

In the following screenshot, we can see user SQLDB is having db_datareader permission in SQLDB database.

Let us try to give db_datareader permission as well.

SID for a database user and SQL login should be similar to prevent any orphan user issues. We can query system tables sys .server_principals and sys.database_principals to check the SID.

Execute the following queries and compare the SID of server principal and database principal.

In the following screenshot, we can see there is similar SID on server principal and database principal for SQLDB user. It is the reason SQL does not treat this as an orphan user.

Before we move further with other DBATools PowerShell commands for Orphan users, prepare the environment with the following steps.

• Drop SQLDB user from destination SQL instance
• Drop existing SQLDB database from destination SQL instance
• Restore SQLDB database again

We can verify that Orphan user exists in SQLDB database in Kashish\SQL2019CTP instance.

Remove-DbaDbOrphanUser in DBATools PowerShell

We might want to drop existing Orphan users in our database. It is simple to drop the orphan users; however if it owns the database objects, we cannot drop it. We need to remap the ownership of all objects for the orphan user to another DB user.

If our earlier example, we do not have any objects ownership for SQLDB users. We can drop it using DBATool command Remove-DbaDbOrphanUser.

As stated earlier, we should check the syntax and parameters before using a command in PowerShell.

In the following command, we want to remove all orphan users from Kashish\SQL2019CTP instance.

It does not give any execution message. It moves the cursor to next line once command execution finish.

Now, connect to SQL instance and expand Databases. In the SQLDB database, expand Security.

We do not see any database user SQLDB in this database. We have removed it using Remove-DbaDbOrphanUser DBATool command.

We can use -Force parameter to drop Orphan users even if they have matching login in SQL instance. If the users own any schema or objects, ownership of those gets changed to dbo.

Let’s perform a demo to view this scenario. First, prepare the environment with the following steps.

1. On the Source database, create a schema and provide ownership of schema to SQLDB user. In database security, right click on schema and create a new schema
   
   

   

2. Take database backup and restore into destination SQL instance with overwriting the existing database.
   
   

   Once we have restored the database, we should see orphan user SQLDB again.

   

Let’s try to drop orphan user using Remove-DbaDbOrphanUser command. Earlier, we created a schema in the source database, but that schema does not own any table or objects. Once we execute DBATools PowerShell command to remove orphan user, it assigns the ownership of particular schema to dbo and drops the orphan user.

We can see a warning message in the output Schema ‘Test’ does not have any underlying object. Ownership will be changed to ‘dbo’ so the user can be dropped. Remember to re-check permissions on this schema!

You can see the owner of the schema is changed to dbo.

Let’s drop the database again from the destination SQL instance. We need to prepare the environment again with the following steps

1. Create a table in the Test schema and insert some records in it
2. Take database backup and restore it on the destination SQL instance

Once we have restored the database, try to drop Orphan user again with Remove-DbaDbOrphanuser command. We cannot drop this orphan user because it owns an object in the database.

Either we need to change the ownership of the object to another user, or we can use -Force parameter to do so. It removes the orphan user by assigning permissions to the dbo user. In the following screenshot, we can see the action Alter owner.

We can verify it from schema properties. It shows schema owner as dbo.

Conclusion

In this article, we explored significant usage of DBATools PowerShell command. We should fix orphan users after database restoration. I hope you liked this article. We will cover more on DBATools command in future articles.

Table of contents

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• Understanding PostgreSQL SUBSTRING function - September 21, 2024
• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023