GRANT VIEW DEFINITION permission to all Stored Procedures in SQL Server
I have restricted all user from viewing script/text of stored procedure, views except DBA this is the part of our security policy for live environment. Generally DBA account should have permission to see the code of SP, View, Trigger etc. in live environment but I have requirement to let one person see script of all SPs. I have simply execute “GRANT VIEW DEFINITION” command if I had to let him view only few SPs but there are 100s of SPs in our database and I have to give him permission for all stored procedures. It is hectic to give permission for each SP manually and that is why I have quickly created one small TSQL script which give permission for “VIEW DEFINITION” on all stored procedures to given user.
TSQL script I am providing here is basically for “VIEW DEFINITION” permission for all “Stored Procedure” but you can twist this TSQL code quickly for your need to give any different permission to any other objects.
Let us first create One sample login and user for AdventureWorks2012 database. If you don’t have AdventureWorks2012 database, you can have it in your test or beta database.
USE [master] GO CREATE LOGIN [TestUser] WITH PASSWORD=N'testing', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE [AdventureWorks2012] GO CREATE USER [TestUser] FOR LOGIN [TestUser] GO
Now, Here is the TSQL script which will give permission to the “TestUser” user in Adventureworks2012 database to view all stored procedures.
DECLARE @tblSPList TABLE ( SPID INT IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED, SPName SYSNAME ) DECLARE @SPName SYSNAME, @RowCounter INT, @RecordCounter INT, @UserName VARCHAR(100), @ExecuteSQL VARCHAR(1000) SET @UserName='TestUser' INSERT INTO @tblSPList (SPName) SELECT '['+ROUTINE_SCHEMA+'].['+ROUTINE_NAME+']' FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' SET @RecordCounter = (SELECT count(*) FROM @tblSPList) SET @RowCounter = 1 WHILE (@RowCounter < @RecordCounter + 1) BEGIN SELECT @SPName = SPName FROM @tblSPList WHERE SPID = @RowCounter SET @ExecuteSQL = N'Grant VIEW Definition on ' + rtrim(cast(@SPName AS VARCHAR(128))) + ' to [' + @UserName +']' --commenting following EXEC statement so that --one can verify before execute --EXEC(@ExecuteSQL) --Print Execute Statement PRINT @ExecuteSQL SET @RowCounter += 1 END GO
You can modify this script to given different permission for different object.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.