sql - 从 SQL Server 获取数据库用户列表及其权限

Question

我需要获得一份报告，该报告将显示数据库用户及其对 SQL Server 的权限。下面是将完成这项工作的查询。

WITH CTE AS
(
SELECT  
[UserName] = CASE princ.[type] 
                WHEN 'S' THEN princ.[name]
                WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
             END,
[UserType] = CASE princ.[type]
                WHEN 'S' THEN 'SQL User'
                WHEN 'U' THEN 'Windows User'
             END,  
[DatabaseUserName] = princ.[name],       
[Role] = null,      
[PermissionType] = perm.[permission_name],       
[PermissionState] = perm.[state_desc],       
[ObjectType] = obj.type_desc,--perm.[class_desc],       
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM    
--database user
sys.database_principals princ  
LEFT JOIN
--Login accounts
sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN        
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
--Table columns
sys.columns col ON col.[object_id] = perm.major_id 
                AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE 
princ.[type] in ('S','U')
 UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
 SELECT  
[UserName] = CASE memberprinc.[type] 
                WHEN 'S' THEN memberprinc.[name]
                WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
             END,
[UserType] = CASE memberprinc.[type]
                WHEN 'S' THEN 'SQL User'
                WHEN 'U' THEN 'Windows User'
             END, 
[DatabaseUserName] = memberprinc.[name],   
[Role] = roleprinc.[name],      
[PermissionType] = perm.[permission_name],       
[PermissionState] = perm.[state_desc],       
[ObjectType] = obj.type_desc,--perm.[class_desc],   
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
 FROM    
--Role/member associations
sys.database_role_members members
 JOIN
--Roles
sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
 JOIN
--Role members (database users)
sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
 LEFT JOIN
--Login accounts
sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
 LEFT JOIN        
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
 LEFT JOIN
--Table columns
sys.columns col on col.[object_id] = perm.major_id 
                AND col.[column_id] = perm.[minor_id]
 LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
 )
SELECT * FROM CTE

这个查询的问题是我需要通过数据库手动运行这个语句，如果我在 SQL Server 上有 10 个数据库，那么我需要运行 10 次才能从不同的数据库中获取报告。

现在我想自动化这项工作，我认为是通过下面的 sql 语句从 sql server 获取数据库列表。

SELECT name
FROM master.dbo.sysdatabases

并将这些结果传递给存储过程。但我被困在那里，非常感谢任何帮助。

Answer 1

有几种方法可以实现这一点。

1) EXEC sp_MSforeachdb @command
您可以使用此类命令在服务器上的每个数据库上运行查询。示例 here .

2) 光标
您也可以使用 Cursor , 例如 here .

3) 动态 SQL
最后一个选项是 IMO 将您的查询重写为 Dynamic SQL .示例 here .

这完全取决于这应该如何工作，结果应该是什么 - 一个带有存储结果的表，或者只是在 SSMS 中查看结果。