SQL Server Orphaned Users - An Invisible Threat: Detection and Remediation Steps

SQL Server Orphaned Users Explained

Orphaned users in SQL Server arise when a database user is associated with a login in the master database that no longer exists and should be removed. This situation can happen when the login is removed or when the database is transferred to a different server lacking the corresponding login.

The SQL Server logins existing on a server instance can be seen through the sys.server_principals catalog view and the sys.sql_logins compatibility view.

Hackers Backdoor to Microsoft SQL Servers using Trigona ransomware

Identify Orphaned Users

For both SQL Server and PDW (Parallel Data Warehouse)

To identify an orphan user in sql server due to missing SQL Server authentication logins, execute the following statement within the user database:

The result displays SQL Server authentication users and their respective security identifiers (SIDs) in the current database that are not connected to any SQL Server logins.

SQL Server Fix Orphaned Users

In the master database, use the CREATE LOGIN statement, including the SID option, to restore a login that is missing. Ensure to input the SID of the database user acquired in the previous step:

To map an orphaned user with a login that already exists in the master database, execute the ALTER USER statement within the user database, indicating the login name.

Once you recreate a missing login, the user can enter the database using the provided password. Following this, the user can adjust the password for the login account using the ALTER LOGIN statement.

IMPORTANT NOTE * Any login has the capability to change its own password. However, only logins possessing the ALTER ANY LOGIN permission can modify the password of another user’s login. Nevertheless, only members of the sysadmin role hold the authority to alter passwords of other sysadmin role members.

Orphaned Users Security Risk

Orphaned users, despite losing login capabilities, retain permissions and database/object access. However, due to their lack of logins, their actions cannot be monitored or audited, making any access using their permissions untraceable.

Orphaned users with high privileges like sysadmin or db_owner in sensitive databases are especially dangerous. Their permissions can potentially be misused by anyone with access to the database

SQL server attacks: mechanisms you must know

Ensure ‘Orphaned Users’ are Dropped From SQL Server Databases

The reason why orphaned users should be removed is to avoid potential misuse of those broken users in any way.

Manual Remediation Steps:

If the orphaned user cannot or should not be matched to an existing or new login using the Microsoft documented process referenced below, run the following T-SQL query in the appropriate database to remove an orphan user:

When engaging in manual remediation steps, it’s important to acknowledge the potential challenges that may arise when following through on each server individually. This approach can be time-consuming and prone to errors, particularly in environments with numerous servers.

Manual remediation may lack consistency, leading to variations in remediation efforts across different servers. Moreover, relying solely on manual remediation increases the likelihood of oversight or incomplete removal of orphaned users, leaving potential security vulnerabilities unaddressed. Therefore, while manual remediation is feasible for a small number of servers, it becomes increasingly impractical and risky as the server count grows.

Automating the remediation process can help mitigate these challenges by ensuring consistency, efficiency, and thoroughness across all servers, thereby enhancing overall security and reducing administrative burden.