如何获得服务器上每个数据库中的每个用户及其角色?
我想我将从这里开始:
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