SQL Server Server 레벨 계정 Role 및 권한 보기
/*2015-02-02 SQLDBWook@gmail.com*/
USE master
go
/*몰아보기*/
SELECT USP.name AS UserID
, SSP.name COLLATE Korean_Wansung_CI_AS AS RoleName
, 'Role' AS DivisionCode
FROM sys.server_role_members AS RM
JOIN sys.server_principals AS SSP
ON RM.role_principal_id = SSP.principal_id
JOIN sys.server_principals AS USP
ON RM.member_principal_id = USP.principal_id
UNION ALL
SELECT USP.name AS UserID
, SPR.permission_name AS PermissionName
, 'Permission' AS DivisionCode
FROM sys.server_principals AS USP
JOIN sys.server_permissions AS SPR
ON USP.principal_id = SPR.grantee_principal_id
WHERE SPR.state = 'G' --Grant만 보기
ORDER BY UserID
, DivisionCode DESC
, RoleName
/*따로보기*/
--#01 ServerRole
SELECT USP.name AS UserID
, SSP.name AS RoleName
, *
FROM sys.server_role_members AS RM
JOIN sys.server_principals AS SSP
ON RM.role_principal_id = SSP.principal_id
JOIN sys.server_principals AS USP
ON RM.member_principal_id = USP.principal_id
--#02 ServerPermissions
SELECT USP.name AS UserID
, SPR.permission_name AS PermissionName
, *
FROM sys.server_principals AS USP
JOIN sys.server_permissions AS SPR
ON USP.principal_id = SPR.grantee_principal_id
'MS SQL Server > Administration&Management' 카테고리의 다른 글
| 대량의 트랜잭션으로 인한 LogShipping 복원 지연시, 최종 복원일자 찾기 (0) | 2015.03.16 |
|---|---|
| Service Broker에서 수행하는 작업 모니터링(sp_broker_works) (0) | 2015.03.10 |
| sp_job (0) | 2014.11.19 |
| SQL Server를 통해 성능카운터 / sys.dm_os_performance_counters 읽기 (0) | 2014.10.29 |
| Impersonate 되어있는 Login의 Grantor와 Grantee 찾기 (0) | 2014.10.28 |