Sql-Server-2008-R2
有沒有辦法找到所有登錄名,以及該登錄名擁有的所有數據庫使用者?
我正在嘗試加入每個登錄和權限表。瀏覽網際網路,但我找不到要列出的內容,例如:
LOGIN | USER | DATABASE_THE_USER_HAS_ACCESS
這是因為我想找到對任何數據庫沒有單一權限的所有使用者(無使用者登錄)。
sys.server_principals AS log LEFT OUTER JOIN sys.syslanguages AS l ON l.name = log.default_language_name LEFT OUTER JOIN sys.server_permissions AS sp ON sp.grantee_principal_id = log.principal_id and sp.type = N'COSQ' LEFT OUTER JOIN sys.credentials AS c ON c.credential_id = log.credential_id LEFT OUTER JOIN master.sys.certificates AS cert ON cert.sid = log.sid LEFT OUTER JOIN master.sys.asymmetric_keys AS ak ON ak.sid = log.sid LEFT OUTER JOIN sys.sql_logins AS sqllog ON sqllog.principal_id = log.principal_id WHERE
我正在查看這些表,但找不到加入它們的模式。
首先,
sp_foreachdb
從 First Responder Kit 中獲取(此處為背景):然後你可以在這裡收集所有的數據庫使用者:
CREATE TABLE #DBUsers ( DBName sysname, DBUserName sysname, DBPrincipalID int, SID varbinary(85), LoginName nvarchar(128), LoginPrincipalID int ); EXEC master.dbo.sp_foreachdb N'INSERT #DBUsers SELECT ''?'',d.name, d.principal_id, d.sid, s.name, s.principal_id FROM ?.sys.database_principals AS d LEFT OUTER JOIN sys.server_principals AS s ON d.sid = s.sid WHERE d.principal_id > 4 AND d.type <> ''R'' AND d.name NOT LIKE N''##%##'';';
現在例如查找在任何數據庫中沒有關聯使用者的任何伺服器級登錄(請注意,這目前將包括那些處於提升角色的使用者,例如
sysadmin
):SELECT * FROM sys.server_principals AS p WHERE type NOT IN ('S','R','C') AND name NOT LIKE N'##%##' AND name NOT LIKE N'NT %\%' AND NOT EXISTS ( SELECT 1 FROM #DBUsers WHERE SID = p.sid );
請注意,您可能需要更深入地探勘,因為可能已將使用者添加到數據庫以明確拒絕訪問。但這對於正常情況應該是一個好的開始。