[스크립트] SQL Server 계정 권한 확인

서버/DB 수준별로 권한을 확인하는 스크립트입니다. 사용할때 적절하게 쿼리를 바꿔가며 쓰는중입니다.

 

--by jyoh. 

--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 1=1--spr.name not in ()
    and spr.name not like '##%'
    and spr.name not like 'NT %'
    and spr.name != 'public'
--  and spm.permission_name != 'CONNECT SQL'
         
order by spr.type,spr.name


------------------------------------------------------
------------------------------------------------------

--2. 유저 수준 권한 (데이터베이스 역할) 확인
      if OBJECT_ID('tempdb..#TEST') is not null
            drop table #TEST
        if OBJECT_ID('tempdb..#TEST2') is not null
            drop table #TEST2
create table #TEST
    (server_name  varchar(200), database_name  varchar(500),
     name  varchar(500),ROLE  varchar(50),type  varchar(50),
    type_desc  varchar(50), default_schema_name   varchar(500)
    )
create table #TEST2
    (server_name  nvarchar(200), name nvarchar(500), database_name nvarchar(max)
    ) 
  
 
Insert into #TEST
 
EXEC sp_msforeachdb '
SELECT       @@servername,''?'' as database_name,dp.name                                     
            ,dp2.name AS ROLE, dp.type, dp.type_desc,
            dp.default_schema_name -- 기본스키마
FROM [?].sys.database_principals AS dp
    INNER JOIN [?].sys.database_role_members AS dr
    ON dp.principal_id = dr.member_principal_id
    INNER JOIN [?].sys.database_principals AS dp2
    ON dr.role_principal_id = dp2.principal_id
    '
 
insert into #TEST2 
select server_name,name, database_name + '('+Role+')' as databasename
from #TEST
where name not in ('public','dbo')
    --and name not like '8%' and name not like '1%'
    --
    and database_name not in ('master','msdb','model')
order by name 
 
select distinct server_name,name,
                stuff((
                    select '/ '+database_name
                    from #Test2 b
                    where b.server_name =a.server_name
                        and b.name = a.name
                    for xml path('')
                ),1,1,'') as database_name
from #test2 a
 drop table #TEST 
 drop table #TEST2
------------------------------------------------------
------------------------------------------------------

--3.  유저 수준 권한 (DB 보안개체/오브젝트) 확인
 
  if OBJECT_ID('tempdb..#TEST3') is not null
            drop table #TEST3
 create table #TEST3
    (server_name  nvarchar(200),  database_name nvarchar(max),name nvarchar(500),objname nvarchar(500),permission_name nvarchar(100),state_desc nvarchar(10)
    ) 
 insert into #TEST3  
EXEC sp_msforeachdb '
SELECT      distinct    @@servername,''?'' as database_name,dpr.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  
       left join [?].sys.database_permissions as dpm on dpr.principal_id = dpm.grantee_principal_id
       inner join [?].sys.database_role_members drm   on dpr.principal_id = drm.member_principal_id
 where permission_name != ''CONNECT''   
    '
    
    
select * from #test3  where database_name not in ('master','msdb' )  
drop table #TEST3