Monday, August 1, 2016

Grant VIEW DEFINITION to all databases stored procedure in SQL Server




/*
Change the user name name in 2nd line and
Replace print @sqlstmt  with exec @sqlstmt to grant VIEW DEFINITION to all databases stored procedure in SQL Server
*/



DECLARE @userName varchar(200)
SET @userName = 'corp\domainuser'

DECLARE @name varchar(50)
DECLARE @spname varchar(50)
DECLARE @schemaname varchar(50)
DECLARE @sqlstmt nvarchar(300)
DECLARE @sqlsel varchar(300)


DECLARE db_cursor CURSOR FOR
SELECT
  name
FROM MASTER.sys.databases
WHERE state_desc = 'ONLINE'
AND is_in_standby = 0
AND name NOT IN ('master', 'model', 'msdb', 'tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
  --Print @name  
  SET @sqlsel = 'DECLARE spro_cursor CURSOR FOR SELECT  SPECIFIC_NAME, SPECIFIC_SCHEMA  from [' + @name + '].information_schema.routines where routine_type = ''PROCEDURE'' and routine_name not like ''dt%'' and routine_name not like ''ms%'' and routine_name not like ''xp%'' '
  -- print @sqlsel
  --DECLARE @spro_cursor CURSOR    
  --  DECLARE spro_cursor CURSOR FOR SELECT  SPECIFIC_NAME, SPECIFIC_SCHEMA  from information_schema.routines where routine_type = 'PROCEDURE'
  EXEC (@sqlsel)
  --DECLARE spro_cursor CURSOR FOR @sqlsel
  OPEN spro_cursor
  FETCH NEXT FROM spro_cursor INTO @spname, @schemaname
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @sqlstmt = 'use [' + @name + ']' + CHAR(13) + CHAR(10) + '  GRANT VIEW DEFINITION ON [' + @schemaname + '].[' + @spname + '] TO [' + @userName + ']'
    PRINT @sqlstmt
    EXECUTE sp_executesql @sqlstmt
    FETCH NEXT FROM spro_cursor INTO @spname, @schemaname
  END
  CLOSE spro_cursor
  DEALLOCATE spro_cursor

  FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor