MS-SQL 2005 형상관리 사용자계정 권한 살펴보기

Database 형상관리 사용자계정 권한 살펴보기

 

작성자 : DBA 이승연

 

어떤 사용자가 sysadmin권한을 가지고 있는지 또는 어떤 사용자가 db_owner권한을 가지고 있는지 우리는 알지 못하며 한 달에 한번씩 DBMS관리대장 이라는 문서를 작성함으로써 그 권한들에 대한 관리를 하게 된다. 하지만, 본 문서 작업이 까다롭기도 하고 작성에 시간도 오래 걸릴뿐더러 정확도도 떨어지는 편이다.

그리하여, 다음과 같은 스크립트를 작성하여 보았다.

 

/*

             해당DBMS에등록되어있는로그인계정목록

*/

SELECT  log.name AS [Name]                      -- 로그인계정

,            log.type_desc                                -- 로그인타입

,            log.is_disabled AS [IsDisabled]       -- 1이면사용할수없음

,            log.create_date AS [CreateDate]    -- 생성일

FROM sys.server_principals AS log

WHERE (

             log.type in ('U', 'G', 'S', 'C', 'K')

             AND log.principal_id not between 101 and 255

             AND log.name <> N'##MS_AgentSigningCertificate##'

) ORDER BY [Name] ASC

 

/*

             해당DBMS에대한서버권한목록

*/

SELECT sp.name               -- 계정이름

,            sp2.name            -- 계정에부여된권한

FROM sys.server_role_members AS srm INNER JOIN sys.server_principals AS sp

ON srm.member_principal_id = sp.principal_id INNER JOIN sys.server_principals AS sp2

ON srm.role_principal_id = sp2.principal_id

 

/*

             각데이터베이스에대한로그인계정정보

*/

EXEC sp_msforeachdb '

SELECT  ''?'' as database_name                  -- 데이터베이스이름

,            dp.name                                        -- 계정이름

,            dp2.name                                      -- 계정역활

,            dp.type                                          -- S : SQL 로그인

                                                                  -- U : Windows 로그인
                                                                  -- G : Windows 그룹

                                                                  -- R : 서버역활

,            dp.type_desc

,            dp.default_schema_name              -- 기본스키마

,            dp.create_date                              -- 생성일

,            dp.modify_date                             -- 수정일

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

'

 

/*

             데이터베이스개체권한

*/

EXEC sp_msforeachdb '

select     ''?''

,            dp.name                                       -- 계정

,            dp.type                                          -- S : SQL 로그인

                                                                  -- U : Windows 로그인

                                                                  -- G : Windows 그룹

                                                                  -- R : 서버역활

,            dp.type_desc                                 -- 로그인타입

,            dp.create_date                              -- 계정생성일

,            dp.modify_date                             -- 계정수정일

,            ds.type                                          -- 데이터베이스사용권한유형

,            ds.permission_name                      -- 데이터베이스사용권한이름

,            ds.state                                         -- 사용상태

                                                                  -- D : 거부

                                                                  -- R : 취소

                                                                  -- G : 허용

,            ds.state_desc                                 -- 상태설명

,            ao.name                                        -- 개체이름

,            ao.type                                          -- 개체형식

                                                                  -- AF : 집계함수(CLR)

                                                                  -- C : CHECK 제약조건

                                                                  -- D : DEFAULT(제약조건또는독립실행형)

                                                                  -- F : FOREIGN KEY 제약조건

                                                                  -- FN : SQL 스칼라함수

                                                                  -- FS : 어셈블리(CLR) 스칼라함수

                                                                  -- FT : 어셈블리(CLR) 테이블반환함수

                                                                  -- IF : SQL 인라인테이블반환함수

                                                                  -- IT : 내부테이블

                                                                  -- P : SQL 저장프로시저

                                                                  -- PC : 어셈블리(CLR) 저장프로시저

                                                                  -- PG : 계획지침

                                                                  -- PK : PRIMARY KEY 제약조건

                                                                  -- R : 규칙(이전스타일, 독립실행형)

                                                                  -- RF : 복제필터프로시저

                                                                  -- S : 시스템기본테이블

                                                                  -- SN : 동의어

                                                                  -- SQ : 서비스큐

                                                                  -- TA : 어셈블리(CLR) DML 트리거

                                                                  -- TF : SQL 테이블반환함수

                                                                   -- TR : SQL DML 트리거

                                                                  -- U : 테이블(사용자정의)

                                                                  -- UQ : UNIQUE 제약조건

                                                                  -- V : 뷰

                                                                  -- X : 확장저장프로시저

,            ao.type_desc                                 -- 개체형삭에대한설명

                                                                  -- AGGREGATE_FUNCTION

                                                                  -- CHECK_CONSTRAINT

                                                                  -- DEFAULT_CONSTRAINT

                                                                  -- FOREIGN_KEY_CONSTRAINT

                                                                  -- SQL_SCALAR_FUNCTION

                                                                  -- CLR_SCALAR_FUNCTION

                                                                  -- CLR_TABLE_VALUED_FUNCTION

                                                                  -- SQL_INLINE_TABLE_VALUED_FUNCTION

                                                                  -- INTERNAL_TABLE

                                                                  -- SQL_STORED_PROCEDURE

                                                                  -- CLR_STORED_PROCEDURE

                                                                  -- PLAN_GUIDE

                                                                  -- PRIMARY_KEY_CONSTRAINT

                                                                  -- RULE

                                                                  -- REPLICATION_FILTER_PROCEDURE

                                                                  -- SYSTEM_TABLE

                                                                  -- SYNONYM

                                                                  -- SERVICE_QUEUE

                                                                  -- CLR_TRIGGER

                                                                  -- SQL_TABLE_VALUED_FUNCTION

                                                                  -- SQL_TRIGGER

                                                                  -- USER_TABLE

                                                                  -- UNIQUE_CONSTRAINT

                                                                  -- VIEW

                                                                  -- EXTENDED_STORED_PROCEDURE

,            ao.create_date                               -- 개체생성일

,            ao.modify_date                             -- 개체수정일

from (select * from [?].sys.database_principals where type = ''U'' or type=''S'') as dp

             inner join [?].sys.database_permissions as ds

             on dp.principal_id = ds.grantee_principal_id

             left join [?].sys.all_objects as ao

             on ds.major_id = ao.object_id

'