每个用户及其在服务器上每个数据库中的角色 [英] Each user and their role in every database on the server

问题描述

如何在服务器上的每个数据库中获取每个用户及其角色?

How do I get each user and their role in every database on the server?

我想我会从这个开始:

    SELECT *
FROM sys.database_role_members drm
INNER JOIN sys.database_principals rp ON drm.role_principal_id = rp.principal_id
INNER JOIN sys.database_principals mp ON drm.member_principal_id = mp.principal_id

推荐答案

我想我明白了:

DECLARE @table TABLE (
    SERVER VARCHAR(100),
    db_name VARCHAR(100),
    db_role VARCHAR(100),
    db_user VARCHAR(100)
    )

INSERT INTO @table
EXEC sp_msforeachdb '
    USE [?];

    SELECT @@SERVERNAME SERVER,
        ''?'' db,
        rp.NAME AS database_role,
        mp.NAME AS database_user
    FROM sys.database_role_members drm
    INNER JOIN sys.database_principals rp ON drm.role_principal_id = rp.principal_id
    INNER JOIN sys.database_principals mp ON drm.member_principal_id = mp.principal_id
    ORDER BY 3
'


SELECT SERVER,
    db_role,
    db_user,
    db_name
FROM @table
WHERE db_name NOT IN (
        'master',
        'tempdb',
        'model',
        'msdb',
        'DBA_UTIL'
        )
ORDER BY 4 DESC,
    2

这篇关于每个用户及其在服务器上每个数据库中的角色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！