Free SQL Help for Devs, DBAs, and the Curious

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.