Ensure 'Orphaned Users' are Dropped From SQL Server Databases

中级别 1自动化mssql

说明

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. Orphan users should be removed to avoid potential misuse of those broken users in any way.

检查方式

审计方法: SQL 查询

sql_query

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;"

预期输出: 0

修复方式

修复方法: 操作指南或基础设施补丁

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>;

属性

操作系统: 任意
标签: mssqlsqlserver-2022
参考: CIS Microsoft SQL Server 2022 Benchmark v1.3.0 - 3.3 Ensure 'Orphaned Users' are Dropped From SQL Server Databases

检查本机

请在您有权审计的机器上运行此命令。扫描为只读操作。

shell

tmaegis scan --target mssql