SQL Server：查询服务器中所有数据库的数据库用户角色

Question

我想查询我的 sql server 实例中所有数据库的数据库用户角色。我修改了 sp_helpuser 的查询：

select  u.name
        ,case when (r.principal_id is null) then 'public' else r.name end
        ,l.default_database_name
        ,u.default_schema_name
        ,u.principal_id
from sys.database_principals u
    left join (sys.database_role_members m join sys.database_principals r on m.role_principal_id = r.principal_id) 
        on m.member_principal_id = u.principal_id
    left join sys.server_principals l on u.sid = l.sid
    where u.type <> 'R'

如何修改它以从所有数据库进行查询？ sys.databases 和 sys.database_principals 之间有什么联系？

Answer 1

您可以使用存储过程sp_msforeachdb

sp_msforeachdb：这是一个非常有用的
系统存储过程将
执行您传递给 for 的任何 SQL 脚本
在 SQL 上的每个数据库中
服务器实例。存储过程
只是循环遍历数据库，
写起来很简单，但是可以节省
您不必自己做。

添加一列作为数据库名称，并为数据库名称添加一个 [?] 占位符，然后在 sp_msforeachdb 存储过程中执行脚本，如下所示：

EXECUTE sp_msforeachdb 'select  ''[?]'' as DatabaseName,
             u.name
            ,case when (r.principal_id is null) then ''public'' else r.name end
            ,l.default_database_name
            ,u.default_schema_name
            ,u.principal_id
    from [?].sys.database_principals u
        left join ([?].sys.database_role_members m join [?].sys.database_principals r on m.role_principal_id = r.principal_id) 
            on m.member_principal_id = u.principal_id
        left join [?].sys.server_principals l on u.sid = l.sid
        where u.type <> ''R'''

要将所有这些都放在一个表中，您必须创建一个数据库中的一张表，我们将使用 master 作为示例。

在 master 数据库中创建表

Create Table master.dbo.userPermissionResults
(
....
)

然后只需将插入语句添加到查询的开头

EXECUTE sp_msforeachdb 'Insert Into master.dbo.userPermissionResults select  ''[?]'' as DatabaseName,
             u.name
            ,case when (r.principal_id is null) then ''public'' else r.name end
            ,l.default_database_name
            ,u.default_schema_name
            ,u.principal_id
    from [?].sys.database_principals u
        left join ([?].sys.database_role_members m join [?].sys.database_principals r on m.role_principal_id = r.principal_id) 
            on m.member_principal_id = u.principal_id
        left join [?].sys.server_principals l on u.sid = l.sid
        where u.type <> ''R'''

您必须为 Insert 语句指定数据库名称，否则它将尝试插入数据存入当前数据库。