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

SQL Server: query database user roles for all databases in server

提问by atricapilla

I would like to make a query for database user roles for all databases in my sql server instance. I modified a query from sp_helpuser:

我想查询我的 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'

How can I modify this to query from all databases? What is the link between sys.databases and sys.database_principals?

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

回答by codingbadger

You could use the stored procedure sp_msforeachdb

您可以使用存储过程 sp_msforeachdb

sp_msforeachdb: This is a very useful system stored procedure that will execute any SQL script you pass to for in each of the databases on your SQL Server instance. The stored procedure just loops through the databases, which is simple to write, but it saves you from having to do it yourself.

sp_msforeachdb：这是一个非常有用的系统存储过程，它将在 SQL Server 实例上的每个数据库中执行您传递给 for 的任何 SQL 脚本。存储过程只是遍历数据库，编写起来很简单，但它使您不必自己动手。

Add a column for the database name and a [?] placeholder for the the database name and then execute the script within the sp_msforeachdbstored proc like this:

为数据库名称添加一列，为数据库名称添加一个 [?] 占位符，然后在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'''

To get this all in one table you would have to create a table in one database, we'll use the masteras an example.

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

Create the table in the masterdatabase

在master数据库中创建表

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

Then simply add the insert statement to the start of the query

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

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

You must specify the database name for the Insertstatement otherwise it will try to insert the data into the current database.

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