Grant execute on all stored procedures in current db

Sample showing how to grant execute on all stored procedures for a specific user in specified database

USE [The Database Name Here]
DECLARE @name nvarchar(128)
DECLARE procCursor CURSOR FOR SELECT Name FROM sysobjects WHERE OBJECTPROPERTY(id, N'IsProcedure') = 1
 
OPEN procCursor
FETCH NEXT FROM procCursor INTO @name
 
WHILE @@FETCH_STATUS= 0
BEGIN
EXEC ('grant execute on [' +@name + '] to[The Database User Here]')
 
FETCH NEXT FROM procCursor INTO @name
END
 
CLOSE procCursor
DEALLOCATE procCursor
GO

Post a comment

Comments closed