如何获取服务器上每个数据库中的每个用户及其角色？
我想我会从这个开始：
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
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
