OM-BP-0017: Remove orphans in systems requiring the declaration of user accounts in multiple sub-systems (Best Practice)

Best Practice

Remove orphans in systems requiring the declaration of user accounts in multiple sub-systems.

Rationale

In some systems, user accounts must be declared in multiple sub-systems. When accesses need to be removed, for example as part of the mover and leaver processes, it may sound attractive to deactivate or remove the user accounts in only one of the sub-systems. This leaves orphan user accounts in the parent system. Such orphan user accounts constitute an attack surface as they may be leveraged to gain unauthorized access to the system and thus represent a risk. See https://open-measure.atlassian.net/wiki/spaces/DIC/pages/1062207530/Orphan+Account+Dictionary+Entry#Definition-3-IAM.

To mitigate this risk, remove orphaned users systematically.

Bad Practices

• Do not identify systems where user accounts must be declared in multiple sub-systems

• Do not document the procedure for the exhaustive removal of user accounts in all sub-systems

• In such systems, do not remove orphaned users

Implementation Details

Quotes

4.3 Drop Orphaned Users From SQL Server Databases (Scored)

Profile Applicability:

Level 1 - SQL Server 2008.x

Description:

A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance and is referred to as orphaned and should be removed.

Rationale:

Orphan users should be removed to avoid potential misuse of those broken users in any way.

Audit:

Run the following T-SQL query to identify orphan users:
EXEC sp_change_users_login @Action='Report';

Remediation:

Run the following T-SQL query to remove an orphan user:
DROP USER <username>;

References:

1. http://msdn.microsoft.com/en-us/library/ms175475(v=sql.100).aspx
2. http://msdn.microsoft.com/en-us/library/ms175475(v=sql.105).aspx

Bibliography

• https://open-measure.atlassian.net/wiki/spaces/BIB/pages/1066729580

Related Best Practices

N/A