andrew4582

GRANT EXECUTE ON SP BY USER

Oct 4th, 2011
261
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
C# 0.74 KB | None | 0 0
  1.  
  2. use [YOUR DATABASE]
  3. GO
  4.  
  5. /*
  6. SELECT * FROM sys.objects
  7. where [type] = 'p' and is_ms_shipped = 0 and name not like 'sp_%'
  8. order by name
  9. */
  10.  
  11. DECLARE @proc VARCHAR(255)
  12. DECLARE @user VARCHAR(255)  
  13. DECLARE @cmd NVARCHAR(500)
  14.  
  15. SET @user = '[NT AUTHORITY\NETWORK SERVICE]'
  16.  
  17. DECLARE ProceduresCursor CURSOR FOR
  18.     SELECT name FROM sys.objects
  19.     WHERE [type] = 'p' and is_ms_shipped = 0    and name not like 'sp_%'
  20.     ORDER BY name
  21.  
  22. OPEN ProceduresCursor
  23. FETCH NEXT FROM ProceduresCursor INTO @proc  
  24. WHILE @@FETCH_STATUS = 0  
  25. BEGIN  
  26.     SET @cmd = 'GRANT EXECUTE ON [dbo].[' + @proc + '] TO ' + @user
  27.     EXEC (@cmd)
  28.     --PRINT @cmd
  29.     FETCH NEXT FROM ProceduresCursor INTO @proc
  30. END
  31. CLOSE ProceduresCursor  
  32. DEALLOCATE ProceduresCursor
Advertisement
Add Comment
Please, Sign In to add comment