SHARE
TWEET

Untitled

a guest Jul 19th, 2019 65 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SET NOCOUNT ON
  2.  
  3. DECLARE @name sysname = N'',
  4.     @proc_definition NVARCHAR(MAX) = N'',
  5.     @proc_permissions NVARCHAR(MAX) = N'',
  6.     @crlf NVARCHAR(4) = CHAR(13) + CHAR(10),
  7.     @print_only BIT = 1
  8.  
  9.  
  10. DECLARE @procs_tbl TABLE (
  11.     [object_id] INT NOT NULL,
  12.     [name] NVARCHAR(256) NOT NULL,
  13.     [proc_definition] NVARCHAR(MAX) NOT NULL
  14. )
  15.  
  16. -- find all the procs with bad options, and jam them into the table variable
  17. -- add the options change, and the if exists drop to the beginning of the script
  18. INSERT INTO @procs_tbl (
  19.     object_id,
  20.     name,
  21.     proc_definition
  22. )
  23. SELECT sm.object_id,
  24.     fn.name,
  25.     N'SET QUOTED_IDENTIFIER, ANSI_NULLS ON;' + @crlf + N'GO' + @crlf +
  26.     N'IF OBJECT_ID(''' + fn.name + ''') IS NOT NULL BEGIN DROP PROCEDURE ' + fn.name + N' END'  + @crlf + N'GO' + @crlf +
  27.     sm.definition  
  28. FROM sys.sql_modules sm
  29. CROSS APPLY (
  30.     SELECT [name] = QUOTENAME(OBJECT_SCHEMA_NAME(sm.object_id)) + '.' + QUOTENAME(OBJECT_NAME(sm.object_id))
  31. ) fn
  32. WHERE (
  33.     sm.uses_ansi_nulls = 0 OR sm.uses_quoted_identifier = 0
  34. ) AND OBJECTPROPERTY(sm.object_id, 'IsMSShipped') = 0
  35. AND OBJECTPROPERTY(sm.object_id, 'IsProcedure') = 1
  36.  
  37. SELECT * FROM @procs_tbl
  38.  
  39. -- generate all the grants off all the procs we are about to recreate
  40. SELECT @proc_permissions += p.state_desc + ' ' + p.permission_name + N' ON '
  41.     + prc.name + N' TO ' + USER_NAME(p.grantee_principal_id) + ';' + @crlf COLLATE Latin1_General_CI_AS
  42. FROM sys.database_permissions p
  43. INNER JOIN @procs_tbl prc ON p.major_id = prc.object_id
  44.  
  45. DECLARE bad_options_cursor CURSOR FAST_FORWARD READ_ONLY FOR
  46. SELECT name, proc_definition
  47. FROM @procs_tbl
  48.  
  49. OPEN bad_options_cursor
  50. FETCH NEXT FROM bad_options_cursor INTO @name, @proc_definition
  51.  
  52. WHILE @@FETCH_STATUS = 0
  53. BEGIN
  54.     PRINT '/********** Altering: ' + @name + ' **********/'
  55.     PRINT @proc_definition        
  56.     IF @print_only = 0 BEGIN   
  57.         EXEC (@proc_definition)
  58.     END
  59.     FETCH NEXT FROM bad_options_cursor
  60.     INTO @name, @proc_definition
  61. END
  62.  
  63. CLOSE bad_options_cursor
  64. DEALLOCATE bad_options_cursor
  65.  
  66. PRINT ''
  67. PRINT 'GO'
  68. PRINT @proc_permissions
  69. IF @print_only = 0 BEGIN   
  70.     EXEC (@proc_permissions)
  71. END
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top