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

No comments:

Post a Comment

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists

If you received below error while attaching a .mdf file in cluster environment please follow below steps to resolve the issue ERROR Ca...