Thursday, October 16, 2008

Grant permissions to view stored procedure text

Sometimes in a production environment you may want to give developers read access to the text of the stored procedures but not be able to modify or execute them. In SQL Server 2005, you can grant VIEW DEFINITION permissions. Here are different ways to it


To grant view permissions to the entire schema


GRANT VIEW DEFINITION ON SCHEMA::dbo TO [UserName]


To grant permissions to specific stored procedure


GRANT VIEW DEFINITION ON YourStoredProcedureName TO [UserName]


To grant view definition on all the stored procedures in the database


--temporary table

DECLARE @tmpTable TABLE (

PK_ID INT IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,

[name] SYSNAME

)

--declare variables

DECLARE @name SYSNAME,

@RowCount INT,

@RecCount INT,

@strSQL VARCHAR(1000)

INSERT INTO @tmpTable ([name])

SELECT ROUTINE_SCHEMA+'.'+ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME NOT LIKE 'dt_%'

-- counters for while

SET @RecCount = (SELECT count(*) FROM @tmpTable)

SET @RowCount = 1

WHILE (@RowCount < @RecCount + 1)

BEGIN

SELECT @name = [name]

FROM @tmpTable

WHERE PK_ID = @RowCount

SET @strSQL = N'Grant VIEW Definition on ' + rtrim(cast(@name AS VARCHAR(128))) + ' to [UserName]'

--Execute the Sql

EXEC(@strSQL)

--Decrement the counter

SET @RowCount = @RowCount + 1

--reset vars, just in case...

SET @name = null

END

SELECT * FROM @tmpTable

7 comments:

RCS said...

Thanks, worked great!

Anonymous said...

Just awesome.......................

Anonymous said...

Thanks. This worked as a charm!

Anonymous said...

excellent post, thanks for the tip!!!

- b said...

Thanks for posting this.

Anonymous said...

Thank you it helped me A lot!!

kishore said...

This really helped me a lot, i was struggling for 2 days to get this, your script worked like a charm for me.