라고 요청을 하면 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
