admin

服务器上每个数据库中的每个用户及其角色

sql

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

我想我将从这里开始:

    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

阅读 146

收藏
2021-06-07

共1个答案

admin

我想我知道了:

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
2021-06-07