Monday, October 15, 2012

Grant execute on all stored procedures

Ever wanted to grant execute permissions on all the user stored procedures for a user?
I had to do this and I ended up writing this cursor. Instead of executing directly, I opted to print the execute commands so that I can review before executing.

DECLARE @spname nvarchar(100)
DECLARE spgrant CURSOR FOR 
select name from sys.objects where type = 'P' AND is_ms_shipped = 0
OPEN spgrant
FETCH NEXT FROM spgrant INTO @spname
WHILE @@FETCH_STATUS = 0
BEGIN 
 Print 'GRANT EXECUTE ON OBJECT::'+@spname+ ' TO Cherry'
FETCH NEXT FROM spgrant INTO @spname
END
CLOSE spgrant

DEALLOCATE spgrant