데이터베이스 역할 추출

--각 데이터베이스에 대한 사용자 계정 정보

 

DECLARE @tmpstr  varchar (1024)

 

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TMP_DB_USER_ROLE')

    DROP TABLE TMP_DB_USER_ROLE

 

GO

 

CREATE TABLE TMP_DB_USER_ROLE

(

Database_Name VARCHAR(20) NOT NULL

, UserName SYSNAME NOT NULL

, UserRole SYSNAME NOT NULL

, type CHAR(1) NOT NULL

, type_desc NVARCHAR(120) NULL

, default_schema_name SYSNAME NULL

)

GO

 

EXEC sp_msforeachdb

'

INSERT INTO TMP_DB_USER_ROLE(Database_Name, UserName, UserRole, type, type_desc, default_schema_name)

SELECT  ''?'' AS Database_Name  -- 데이터베이스이름

,dp.name AS UserName            -- 계정이름

,dp2.name AS UserRole           -- 계정역활

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

                                -- U : Windows 로그인

                                -- G : Windows 그룹

                                -- R : 서버역활

,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

WHERE dp2.name LIKE ''%db_jkuser%''

        AND ''?'' != ''master''

'

GO

 

--데이터 베이스별 사용자 역할 추가

SELECT 'USE ' + Database_Name + '

GO

exec sp_addrolemember '+'[' + 'db_datawriter' +'], '+ UserName + ';'

+ char(13) FROM TMP_DB_USER_ROLE

GO

 

--데이터 베이스별 사용자 역할 추가

SELECT 'USE ' + Database_Name + '

GO

exec sp_addrolemember '+'[' + 'db_ddladmin' +'], '+ UserName + ';'

+ char(13) FROM TMP_DB_USER_ROLE

GO

 

 

--데이터베이스별 로그인

SELECT 'USE' + Database_Name + '

GO

ALTER USER ' + UserName + ' WITH DEFAULT_SCHEMA = ' + default_schema_name  + ';'

+char(13) FROM TMP_DB_USER_ROLE