/*
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