권한 관리^^

-- 권한 생성

-- 로그인 생성
exec sp_addlogin 'judy','1136','AdventureWorks'

-- db access 권한
use AdventureWorks
go

exec sp_grantdbaccess 'judy'
go
-- db roll 권한

exec sp_addrolemember 'db_owner','judy'
-- exec sp_addsrvrolemember '아이디','sysadmin'

-- 기본 스키마 변경
alter user judy
 WITH DEFAULT_SCHEMA = dbo;
go

---------------------------------------------------------
-- 마스터 프로시져 만들기
USE MASTER
GO

IF OBJECT_ID ('SP_PRIV') IS NOT NULL
DROP PROC SP_PRIV
GO

CREATE PROC SP_PRIV
(
@SELECT_SQL VARCHAR(8000) = ''
, @EXECUTE_SQL VARCHAR(8000) = ''
, @EXECUTE INT = 0
)

AS

DECLARE
   @CNT INT
 , @IDX INT
 , @OBJNAME VARCHAR(1000)
 , @SQL VARCHAR(8000)
 , @NOT_IN_LIST VARCHAR(8000)

SELECT @CNT = 0 , @OBJNAME ='', @SQL =''

IF OBJECT_ID ('TEMPDB..##TBLX') IS NOT NULL DROP TABLE ##TBLX

CREATE TABLE ##TBLX ( IDX INT IDENTITY(1,1), OBJNAME VARCHAR(1000))
SET @NOT_IN_LIST ='
  AND NAME NOT IN (
  ''dt_whocheckedout_u'',''dt_whocheckedout'',''dt_verstamp007'',''dt_verstamp006'',''dt_vcsenabled'',''dt_validateloginparams_u''
  ,''dt_validateloginparams'',''dt_setpropertybyid_u'',''dt_setpropertybyid'',''dt_removefromsourcecontrol'',''dt_isundersourcecontrol_u''
  ,''dt_isundersourcecontrol'',''dt_getpropertiesbyid_vcs_u'',''dt_getpropertiesbyid_vcs'',''dt_getpropertiesbyid_u'',''dt_getpropertiesbyid''
  ,''dt_getobjwithprop_u'',''dt_getobjwithprop'',''dt_generateansiname'',''dt_dropuserobjectbyid'',''dt_droppropertiesbyid''
  ,''dt_displayoaerror_u'',''dt_displayoaerror'',''dt_checkoutobject_u'',''dt_checkoutobject'',''dt_checkinobject_u'',''dt_checkinobject''
  ,''dt_adduserobject_vcs'',''dt_adduserobject'',''dt_addtosourcecontrol_u'',''dt_addtosourcecontrol'')
 '
SET @SQL = 'INSERT INTO ##TBLX ' + REPLACE(@SELECT_SQL, 'NOT_IN_LIST', @NOT_IN_LIST)

EXEC (@SQL)

SELECT @CNT = COUNT(*) FROM ##TBLX

WHILE (@CNT <> 0 )
 BEGIN
  SELECT @IDX = IDX, @OBJNAME = OBJNAME FROM ##TBLX  WHERE IDX = @CNT
  SET @EXECUTE_SQL = REPLACE(@EXECUTE_SQL , 'REPLACE_OBJNAME', @OBJNAME) 
  IF @EXECUTE = 1
   BEGIN
   EXEC (@EXECUTE_SQL)
   PRINT 'OK'
   END
  PRINT @EXECUTE_SQL
  SET @EXECUTE_SQL = REPLACE(@EXECUTE_SQL , @OBJNAME, 'REPLACE_OBJNAME') 
  SET @CNT = @CNT - 1
 END
GO

-- master db에 권한 생성 관련 프로시져 생성해 두고

-- 모든 데이터베이스 억세스 가능하게

EXEC SP_PRIV  
 @SELECT_SQL = '
 SELECT  NAME
 FROM MASTER.DBO.SYSDATABASES
 WHERE NAME NOT IN (''TEMPDB'',''MASTER'',''MSDB'',''MODEL'')
 ORDER BY NAME
 '
 , @EXECUTE_SQL =  'exec REPLACE_OBJNAME.dbo.sp_grantdbaccess ''user'''
 , @EXECUTE = 1

-- db_owner 권한 주기

EXEC SP_PRIV  
 @SELECT_SQL = '
 SELECT  NAME
 FROM MASTER.DBO.SYSDATABASES
 WHERE NAME NOT IN (''TEMPDB'',''MASTER'',''MSDB'',''MODEL'')
 ORDER BY NAME
 '
 , @EXECUTE_SQL =  'exec REPLACE_OBJNAME.dbo.sp_addrolemember ''db_owner'', ''user'''
 , @EXECUTE = 1