Sql-Server-2008-R2

有沒有辦法找到所有登錄名,以及該登錄名擁有的所有數據庫使用者?

  • October 1, 2018

我正在嘗試加入每個登錄和權限表。瀏覽網際網路,但我找不到要列出的內容,例如:

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

請注意,您可能需要更深入地探勘,因為可能已將使用者添加到數據庫以明確拒絕訪問。但這對於正常情況應該是一個好的開始。

引用自:https://dba.stackexchange.com/questions/219006