Detecting Orphaned Users in SQL Server and Fixing Them

By Tom Nonmacher

In the world of database management, maintaining a secure and seamless operation often requires the detection and resolution of issues before they escalate into serious problems. One such common issue in SQL Server is the presence of orphaned users, which are users that exist in the database but are disconnected from their respective logins at the server level. This scenario typically arises when a database is moved to a different server or restored from a backup. In this blog post, we will discuss how to detect orphaned users in SQL Server 2016 and 2017, MySQL 5.7, DB2 11.1, and Azure SQL, and how to fix them.

In SQL Server, orphaned users can be identified by executing a system stored procedure called 'sp_change_users_login'. This procedure, when used with the '@Action' parameter set to 'Report', generates a list of orphaned users. Let's take a look at an example: 


EXEC sp_change_users_login @Action='Report';

Once the orphaned users are identified, they can be linked to an existing login by using the 'sp_change_users_login' procedure with the '@Action' parameter set to 'Update_One'. The '@UserNamePattern' and '@LoginName' parameters should be set to the name of the orphaned user and the login name, respectively. 


EXEC sp_change_users_login @Action='Update_One', @UserNamePattern='orphaneduser', @LoginName='existinglogin';

In MySQL 5.7, the process is slightly different. Orphaned users can be identified by querying the 'mysql.user' table for any user that doesn't have a corresponding entry in the 'mysql.db' table. Here's an example on how to do this: 


SELECT User, Host FROM mysql.user 
LEFT JOIN mysql.db ON mysql.user.User = mysql.db.User
WHERE mysql.db.Db IS NULL;

For DB2 11.1, orphaned users can be identified by running a query on the 'SYSCAT.DBAUTH' system catalog view, to find any user that doesn't have a corresponding entry in the 'SYSIBMADM.PRIVILEGES' view. Here's an example: 


SELECT grantee FROM SYSCAT.DBAUTH
EXCEPT 
SELECT authid FROM SYSIBMADM.PRIVILEGES;

Finally, for Azure SQL, the process is similar to SQL Server. However, since Azure SQL doesn’t support the 'sp_change_users_login' system stored procedure, you will need to use the 'ALTER USER' statement to fix the orphaned users. Here's an example: 


ALTER USER orphaneduser WITH LOGIN = existinglogin;

Detecting and fixing orphaned users is an essential part of database management. By utilizing the correct system procedures and queries as shown above, you can ensure the integrity and security of your databases across various platforms.

Check out the latest articles from all our sites:

Privacy Policy for sqlsupport.org

Last updated: Feb 03, 2026

sqlsupport.org respects your privacy and is committed to protecting any personal information you may provide while using this website.

This Privacy Policy document outlines the types of information that are collected and recorded by sqlsupport.org and how we use it.

Information We Collect

  • Internet Protocol (IP) addresses
  • Browser type and version
  • Pages visited
  • Time and date of visits
  • Referring URLs
  • Device type

Cookies and Web Beacons

sqlsupport.org uses cookies to store information about visitors preferences and to optimize the users experience.

How We Use Your Information

  • Operate and maintain our website
  • Improve user experience
  • Analyze traffic patterns
  • Prevent fraudulent activity

Contact

Email: admin@sqlsupport.org




19CB1B
Please enter the code from the image above in the box below.