Scripts to grant all SQL objects permission

Scripts to grant select permission on all user tables to a database role

SELECT 'grant select on ' + TABLE_SCHEMA + '.' + TABLE_NAME + ' to DB_ROLE'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME

Using the undocumented sp_MSForEachTable stored procedure

EXEC sp_MSForEachTable 'grant select on ? to DB_ROLE'

Scripts to grant select permission on all views to a database role

SELECT 'grant select on ' + TABLE_SCHEMA + '.' + 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 ' + schema_Name(schema_id) + '.' + NAME + ' to DB_ROLE'
FROM sys.procedures
WHERE [TYPE] = 'P'
ORDER BY NAME

Scripts to grant execute permission on all scalar-valued functions to a database role

SELECT 'grant execute on ' + schema_Name(schema_id) + '.' + NAME + ' to DB_ROLE'
FROM sys.objects 
WHERE type = 'FN'

Scripts to grant select permission on all table-valued functions to a database role

  • IF - Inline Table-Valued Function
  • TF - Multi-Statement Table-Valued Function
SELECT 'grant select on ' + schema_Name(schema_id) + '.' + NAME + ' to DB_ROLE'
FROM sys.objects 
WHERE type IN ('IF','TF')

Listing permissions on schema objects within a database

SELECT pr.principal_id, pr.name, pr.type_desc, 
    pr.authentication_type_desc, pe.state_desc, 
    pe.permission_name, s.name + '.' + o.name AS ObjectName
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id
JOIN sys.objects AS o
    ON pe.major_id = o.object_id
JOIN sys.schemas AS s
    ON o.schema_id = s.schema_id
WHERE  pr.name = 'DB_ROLE'
and  o.name IN ('OBJECT_NAME')