본문 바로가기
㏈ª By β┖υΕJini/MS-SQL

저장 프로시져 일괄 실행 권한 주기[TIP]

by ㏈ª ☞ β┖υΕJini.κR 2009. 10. 22.


사용자 삽입 이미지
 해외쪽 DB 관리자에게 사용 프로시져 실행 권한만 주시기 바랍니다.
 라고 요청을 하면 EM 이나 SSMS 에서 마우스로 열심히 클릭질 하고 계신다.
 그 많은 프로시져를 언제 클릭질 하실껀가요.
 이런 분들을 위해~~~~~~~~~~~~


CREATE
PROCEDURE spGrantExectoStoredProcs

 @user sysname,

 @PROC_SEARCH varchar(10) = '' -- 특정프로시져 LIKE 검색조건자

AS

/*----------------------------------------------------------------------------

-- Object Name: spGrantExectoAllStoredProcs

-- Author: Edgewood Solutions

-- Development Date: 03.19.2007

-- Called By: TBD

-- Description: Issue GRANT EXEC statement for all stored procedures

-- based on the user name that is passed in to this stored procedure

-- Project: SQL Server Security

-- Database: User defined databases

-- Business Process: SQL Server Security

--

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

-- Num | CRF ID | Date Modified | Developer | Description

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

-- 001 | N\A      | 03.15.2007    | Edgewood | Original code for the GRANT

-- 002 Bluejini | Modify code for the GRANT ADD @PROC_SEARCH

-- EXEC process

--

*/

SET NOCOUNT ON

 

-- 1 - 변수선언

DECLARE @CMD1 varchar(8000)

DECLARE @MAXOID int

DECLARE @OwnerName varchar(128)

DECLARE @ObjectName varchar(128)

 

-- 2 - 임시테이블생성

CREATE TABLE #StoredProcedures

(OID int IDENTITY (1,1),

StoredProcOwner varchar(128) NOT NULL,

StoredProcName varchar(128) NOT NULL)

 

-- 3 - 임시테이블에프로시져리스트추가

INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)

SELECT ROUTINE_SCHEMA, ROUTINE_NAME

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_NAME NOT LIKE 'dt_%'

AND ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME like @PROC_SEARCH

 

-- 4 - 임시테이블MAX

SELECT @MAXOID = MAX(OID) FROM #StoredProcedures

 

-- 5 - 루프

WHILE @MAXOID > 0

BEGIN

 

-- 6 - 변수정의

SELECT @OwnerName = StoredProcOwner,

@ObjectName = StoredProcName

FROM #StoredProcedures

WHERE OID = @MAXOID

 

-- 7 - Build the string

SELECT @CMD1 = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO ' + @user

 

-- 8 - Execute the string

 

EXEC(@CMD1)

 

-- 9 - Decrement @MAXOID

SET @MAXOID = @MAXOID - 1

END

 

-- 10 - 임시테이블삭제

DROP TABLE #StoredProcedures

 

SET NOCOUNT OFF

GO

 

 

EXEC spGrantExectoStoredProcs 'Login1','Usp_%'

창고 사이트 : http://snippets.dzone.com/posts/show/4688