Scripts to grant select permission on all user tables to a database role
SELECT 'grant select on ' + TABLE_NAME + ' to DB_ROLE'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
Scripts to grant select permission on all views to a database role
SELECT 'grant select on ' + TABLE_NAME + ' to DB_ROLE'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
ORDER BY TABLE_NAME
Scripts to grant select permission on all views to a database role
SELECT 'grant select on ' + TABLE_NAME + ' to DB_ROLE'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
ORDER BY TABLE_NAME
Scripts to grant execute permission on all stored procedures to a database role
SELECT 'grant execute on ' + NAME + ' to DB_ROLE'
FROM sys.procedures
WHERE [TYPE] = 'P'
ORDER BY NAME