Execute the following
script generator Microsoft SQL Server T-SQL query in SSMS Query Editor after setting the results
to TEXT mode. Paste the resulting script back to the query window
for deployment execution:
USE pubs;
DECLARE @DBrole sysname
SET @DBrole = 'RoleDELTA'
-- SQL execute permission check
PRINT 'Not permissioned'
SELECT name FROM sysobjects
WHERE xtype='P' AND base_schema_ver < 16
AND id NOT IN
( SELECT o.id FROM syspermissions p
LEFT JOIN sysusers u ON p.grantee = u.uid
LEFT JOIN sysobjects o ON p.id = o.id
WHERE lower(u.name) = lower(@DBrole)
)
-- SQL grant execute - generate statements
PRINT 'GRANT EXECUTE generation '
SELECT 'GRANT EXECUTE ON [' + name + '] TO [' + @DBrole + ']'
FROM sysobjects
WHERE xtype='P' AND base_schema_ver <> 16
AND id NOT IN
( SELECT o.id FROM syspermissions p
LEFT JOIN sysusers u ON p.grantee = u.uid
LEFT JOIN sysobjects o ON p.id = o.id
WHERE lower(u.name) = lower(@DBrole)
)
GO
/* Partial results
GRANT EXECUTE ON [byroyalty] TO [RoleDELTA]
GRANT EXECUTE ON [CustomerListByState] TO [RoleDELTA]
*/
Related article:
http://www.sqlusa.com/bestpractices/grantexectorole/
|
