Rajendra Gupta
SQL LOgin

Fix Orphan users in SQL Server using DBATools PowerShell

April 30, 2019 by

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.

Orphan users 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.

SQL login

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

SQL login

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.

Orphan users  error

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.

Orphan user

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

Orphan users

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.

SID for Orphan users

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.

DBATools PowerShell.

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.

DBATools PowerShell.

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.

DBATools PowerShell.

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.

DBATools PowerShell for a database

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.

fix orphan user

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.

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

fix orphan users

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

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.

SQL LOgin

Let us try to give db_datareader permission as well.

SQL login

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.

SID login and 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.

FIx orphan user

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.

fix orphan user

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. Remove orphan user

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.

Verify login

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

    Verify schema owner

  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.

    Verify orphan user

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!

Error while dropping orphan user

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

Schema owner

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.

DBATools Powershell

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.

DBATools Powershell

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

Schema owner

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

DBATools PowerShell Module for SQL Server
PowerShell SQL Server Validation Utility – DBAChecks
SQL Database Backups using PowerShell Module – DBATools
IDENTITY columns threshold using PowerShell SQL Server DBATools
DBATools PowerShell SQL Server Database Backups commands
SQL Restore Database using DBATools
Validate backups with SQL restore database operations using DBATools
Fix Orphan users in SQL Server using DBATools PowerShell
Creating a SQL Server Database using DBATools
Get SQL Database details using DBATools
Get-DbaHelpIndex command in DBATools

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
965 Views