SQL Server Orphaned Users - Detection and Remediation Steps

SQL Server Orphaned Users Explained

Orphaned users 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

SIDs Explained

Logins SID and User SIDs (Security Identifiers) are intertwined concepts in SQL Server security, particularly when dealing with login types other than Windows Authentication

Login SID

A login SID is a unique identifier like a fingerprint assigned to a SQL Server login differentiating one login from another. This SID is stored internally by SQL Server and used to track the login’s activity and permissions.

User SID

A User SID can refer to two things depending on the context:

SQL Server User SID: Similar to a login SID, a SQL Server user (created within the database) can also have its own SID. This SID helps manage user permissions within the specific database.

Mapped Login SID: When a SQL Server login is linked to a database user (common for SQL Server logins, not Windows Authentication), the login’s SID acts as the user SID within the database. This establishes the connection between the login and the database user’s permissions.

Identify SQL Orphaned Users

For both SQL Server and PDW (Parallel Data Warehouse)

To identify SQL orphan user 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.

SQL Orphaned Users Security Risk

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

Audit: 

Run the following T-SQL query in each database to identify orphan users. No rows should be returned.

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.