/*------------------------------------------------------------------------------
Assign explicit permission to User defined SPs
1) Correct the DB name in USE statement before the execution.
2) Assign the correct user to the @USER.
-------------------------------------------------------------------------------*/
SET NOCOUNT ON
USE TEST -- CHANGE THE DB HERE
DECLARE @USER VARCHAR(50)
SET @USER='TEST2'----CHANGE THE USER HERE
DECLARE @SECUREPERMISSION TABLE
(
ID INT IDENTITY(1,1),
OBJNAME VARCHAR(100),
SCHNAME VARCHAR(100)
)
INSERT INTO @SECUREPERMISSION
SELECT O.NAME,S.NAME FROM SYS.OBJECTS O
INNER JOIN SYS.SCHEMAS S ON O.SCHEMA_ID=S.SCHEMA_ID
WHERE TYPE='P'
DECLARE @COUNT INT, @PROCESSCOUNT INT, @CMD VARCHAR(200)
SET @COUNT=1
SELECT @PROCESSCOUNT= COUNT(ID) FROM @SECUREPERMISSION
WHILE @COUNT<=@PROCESSCOUNT
BEGIN
SET @CMD='GRANT EXECUTE ON'
SELECT @CMD= @CMD +' '+SCHNAME+'.'+ OBJNAME +'' FROM @SECUREPERMISSION WHERE ID=@COUNT
SET @CMD=@CMD+ ' TO ' +@USER
EXECUTE (@CMD)
--SELECT @CMD
SET @COUNT=@COUNT+1
END
SET NOCOUNT OFF
