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'
Latest posts by Ken Spencer (see all)
- Automating software development - October 7, 2014
- Uses for SQL Server 2014 - May 21, 2014
- Query to find in-memory tables in SQL Server 2014 database - April 29, 2014