권한 조회

SQL Server 2005 이상

1. 서버 수준 권한

--======================
--1. 서버수준의권한
--======================

select  spr.name, spr.type, spr.type_desc,
             spr.default_database_name, spr.default_language_name,
             spm.class_desc, spm.permission_name, spm.state_desc,
             suser_name(srm.role_principal_id) as server_role_name
from sys.server_principals as spr with (nolock)
       inner join sys.server_permissions as spm (nolock) on spm.grantee_principal_id = spr.principal_id
       left join sys.server_role_members as srm with (nolocK) on spr.name = suser_name(srm.member_principal_id)
where spr.type in ('S','U','K', 'G') --C, R
       --and spr.name = ''  -- 유저로하나찾기
order by spr.type,spr.name

-- 유저하나의role member 보기
select suser_name(role_principal_id), suser_name(member_principal_id)
from sys.server_role_members where member_principal_id = suser_id('')

2. DB 수준 권한

use testdb
go

EXEC sp_dbfixedrolepermission;
GO

SELECT * FROM sys.fn_builtin_permissions(DEFAULT)

SELECT * FROM sys.fn_builtin_permissions(DEFAULT) 
    WHERE permission_name = 'SELECT';


-- 사용자 role 고정 role 권한 정보
SELECT DPR.NAME, IS_FIXED_ROLE
	--,DPR.TYPE_DESC 
	--,USER_NAME(DRM.MEMBER_PRINCIPAL_ID) AS USER_NAME
    ,USER_NAME(DRM.ROLE_PRINCIPAL_ID) AS ROLE_NAME
FROM  SYS.DATABASE_PRINCIPALS AS DPR WITH (NOLOCK)
  LEFT JOIN SYS.DATABASE_ROLE_MEMBERS DRM WITH (NOLOCK)  ON DPR.PRINCIPAL_ID = DRM.MEMBER_PRINCIPAL_ID
WHERE DPR.TYPE ='R'
ORDER BY DPR.NAME

-- role별 사용자 정보
SELECT  USER_NAME(DRM.ROLE_PRINCIPAL_ID) AS 'ROLE_NAME'
	,USER_NAME(DRM.MEMBER_PRINCIPAL_ID) AS 'LOGIN'
FROM SYS.DATABASE_ROLE_MEMBERS  AS DRM WITH(NOLOCK) 
	JOIN SYS.DATABASE_PRINCIPALS AS DPM WITH(NOLOCK) ON DRM.ROLE_PRINCIPAL_ID = DPM.PRINCIPAL_ID
WHERE DPM.TYPE ='R'
	AND DPM.IS_FIXED_ROLE = 0
ORDER BY USER_NAME(DRM.ROLE_PRINCIPAL_ID) 

select dpr.name, dpr.type_desc, dpr.default_schema_name,
       user_name(drm.member_principal_id) as user_name,
       user_name(drm.role_principal_id) as role_name
from  sys.database_principals as dpr with (nolock)
       inner join sys.database_role_members drm with (nolock)  on dpr.principal_id = drm.member_principal_id
--where name = ''  -- role이나 유저정보
order by 1,2


-- DB별로 사용자와 ROLE이 어떤 객체의 권한을 가지고 있는지 여부
select dpr.name, dpr.type_desc, dpr.default_schema_name,
	          dpm.class_desc,
	          case  when dpm.major_id = 0 then 'ALL' else obj.name end as object_name,
             dpm.permission_name, dpm.state_desc
from sys.database_principals as dpr with (nolock)
       left join sys.database_permissions as dpm with (nolock) on dpr.principal_id = dpm.grantee_principal_id
       left outer join sys.all_objects as obj with (nolock) on dpm.major_id = obj.object_id
--where dpr.name = '' -- 하나의유저나 혹은 role 이름이나 모두 가능
order by dpr.type, dpr.name



-- 사용자 role member 상세 보기
select dpr.name, dpr.type_desc, dpr.default_schema_name,
       user_name(drm.member_principal_id) as user_name,
       user_name(drm.role_principal_id) as role_name,
	   case  when dpm.major_id = 0 then 'ALL' else OBJECT_NAME(dpm.major_id)  end as object_name,
             dpm.permission_name, dpm.state_desc
from  sys.database_principals as dpr with (nolock)
       left join sys.database_permissions as dpm with (nolock) on dpr.principal_id = dpm.grantee_principal_id 
       inner join sys.database_role_members drm with (nolock)  on dpr.principal_id = drm.member_principal_id
	   --left outer join sys.all_objects as obj with (nolock) on dpm.major_id = obj.object_id
	   -- 안해도 된다.
--where dpr.type = 'R' -- DATABASE_ROLE 
		--and dpr.name = ''		
	/* S = SQL 사용자
	   U = Windows 사용자
       G = Windows 그룹
       A = 응용 프로그램 역할
       R = 데이터베이스 역할
       C = 인증서로 매핑된 사용자
       K = 비대칭 키로 매핑된 사용자 */
ORDER BY dpr.name, 5

3. 권한 제거 Script 생성

-- object 권한 제거
select schema_name(obj.schema_id) as schema1,
	   case  when dpm.major_id = 0 then 'ALL' else OBJECT_NAME(dpm.major_id)  end as object_name,
       dpm.state_desc, 
     ('REVOKE ' + dpm.permission_name COLLATE  Korean_Wansung_CI_AS
     + ' ON OBJECT::' + schema_name(obj.schema_id)+ '.' + OBJECT_NAME(dpm.major_id) + ' FROM '  + dpr.name ) 
from  sys.database_principals as dpr with (nolock)
       inner join sys.database_permissions as dpm with (nolock) on dpr.principal_id = dpm.grantee_principal_id 
       inner join sys.objects  as obj with (nolock) on dpm.major_id = obj.object_id
where  dpr.name  =''   and  dpm.permission_name = 'EXECUTE'

	  

SQL Server 2000 이상

--==========================
-- 2000용
--==========================
select name, createdate, updatedate, dbname from master..syslogins
where  name != 'sa' and name not like '#%' order by name;

exec sp_helpsrvrolemember

exec sp_helprolemember

-- 객체정보
select distinct table_catalog, grantee, table_name , PRIVILEGE_TYPE
     --('REVOKE ' + PRIVILEGE_TYPE + ' ON ' + table_name+ ' FROM '  + grantee ) 
from INFORMATION_SCHEMA.COLUMN_PRIVILEGES

select grantee, table_catalog, table_schema, table_name, privilege_type, is_grantable
from INFORMATION_SCHEMA.TABLE_PRIVILEGES
where grantee = ''

select specific_catalog, specific_name, * from INFORMATION_SCHEMA.PARAMETERS