One of my favorite features in TSQL is the INFORMATION_SCHEMA views. They give you access to all sorts of information about SQL server.I frequently have a problem where i need to grant execute to a bunch of stored procedures to a particular user. On a large project, thats quite a bit of work. So i created this little query that will automatically generate the grant execute statements. Just run it, copy the output into a query window and hit F5.



select 'GRANT EXECUTE ON ' + ROUTINE_SCHEMA + '.' +  ROUTINE_NAME + ' TO [myuser]'
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE'