TMA-MSSQL-3.3
Ensure 'Orphaned Users' are Dropped From SQL Server Databases
Descrição
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. Iterates every online database; limited to SQL, Windows user and Windows group principals. Runs one sqlcmd query with its defaults (Windows authentication to the local default instance); set SQLCMDSERVER, SQLCMDUSER and SQLCMDPASSWORD for another instance or SQL authentication. Needs VIEW SERVER STATE / sysadmin-level read access.
Como é verificado
Método de auditoria: Consulta SQL
sqlcmd -b -h -1 -W -Q "SET NOCOUNT ON; DECLARE @n int = 0, @k int, @db sysname, @s nvarchar(max); DECLARE c CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM sys.databases WHERE state = 0 AND HAS_DBACCESS(name) = 1; OPEN c; FETCH NEXT FROM c INTO @db; WHILE @@FETCH_STATUS = 0 BEGIN SET @s = N'USE ' + QUOTENAME(@db) + N'; SELECT @k = COUNT(*) FROM (SELECT DP.name FROM sys.database_principals DP WHERE DP.sid NOT IN (SELECT L.sid FROM sys.syslogins L WHERE L.sid = DP.sid) AND (SELECT containment FROM sys.databases WHERE name = DB_NAME()) = 0 AND DP.type IN (''S'',''U'',''G'') AND DP.type_desc NOT IN (''DATABASE_ROLE'',''APPLICATION_ROLE'') AND DP.name NOT IN (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'',''MS_DataCollectorInternalUser'') AND NOT (DP.name = ''cdc'' AND (SELECT is_cdc_enabled FROM sys.databases WHERE name = DB_NAME()) = 1)) AS x;'; EXEC sp_executesql @s, N'@k int OUTPUT', @k = @k OUTPUT; SET @n = @n + @k; FETCH NEXT FROM c INTO @db; END; CLOSE c; DEALLOCATE c; SELECT @n;"- Saída esperada
0
Como é corrigido
Método de correção: Orientação ou patch de infraestrutura
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: USE <database_name>; GO DROP USER <username>;
Propriedades
- Sistemas operacionais
- Qualquer
- Tags
- mssqldatabaseauthentication
- Referência
- CIS Microsoft SQL Server 2025 Benchmark v1.0.0 - 3.3 Ensure 'Orphaned Users' are Dropped From SQL Server Databases
Verificar este host
Execute isto em uma máquina que você tenha permissão para auditar. A varredura é somente leitura.
tmaegis scan --target mssql