728x90
반응형
SELECT L.name,
ISNULL(R.db_datareader,'') [db_datareader],
ISNULL(R.db_datawriter,'') [db_datawriter],
ISNULL(R.db_ddladmin,'') [db_ddladmin],
ISNULL(R.db_owner,'') [db_owner],
ISNULL(R.eland_operator,'') [eland_operator],
ISNULL(R.sysadmin,'') [sysadmin]
FROM sys.syslogins AS L
LEFT JOIN (
SELECT DatabaseUserName,
case when [db_datareader] is null then '' else 'O' end as [db_datareader],
case when [db_datawriter] is null then '' else 'O' end as [db_datawriter],
case when [db_ddladmin] is null then '' else 'O' end as [db_ddladmin],
case when [db_owner] is null then '' else 'O' end as [db_owner],
case when [eland_operator] is null then '' else 'O' end as [eland_operator],
case when [sysadmin] is null then '' else 'O' end as [sysadmin]
FROM ( SELECT isnull (DP2.name, 'No members') AS DatabaseUserName,DP1.name AS DatabaseRoleName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
UNION
SELECT name,'sysadmin' as DatabaseRoleName
FROM sys.syslogins where sysadmin=1 and name not like 'NT SERVICE%' ) AS result
PIVOT ( MAX(DatabaseRoleName) FOR DatabaseRoleName IN ([db_datareader],[db_datawriter],[db_ddladmin],[db_owner],[eland_operator],[sysadmin])) AS pivot_result
WHERE DatabaseUserName NOT IN ('dbo','No members')
) AS R ON L.name=R.DatabaseUserName
WHERE L.name not like '##%' AND L.name not like 'NT %'
ORDER BY R.DatabaseUserName
728x90
반응형
'운영 > 권한 관리' 카테고리의 다른 글
DBUser에 대한 DBRole 부여 현황 (0) | 2022.06.30 |
---|---|
public Role에 부여된 권한 확인 (0) | 2022.03.25 |
Register a SPN for SQL Server Authentication with Kerberos (0) | 2021.05.18 |
The database principal owns a schema in the database, and cannot be dropped. (0) | 2021.03.12 |
데이터베이스 부여된 권한 조회 (0) | 2020.10.19 |