|
The following
Microsoft SQL Server T-SQL script will generate a permissioning script which can be executed in SSMS Query Editor window or SQLCMD as file input (-i option):
USE AdventureWorks;
GO
DECLARE @DBrole SYSNAME
SET @DBrole = 'RoleX'
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))
PRINT 'GRANT EXECUTE generation '
SELECT 'GRANT EXECUTE ON ' + QUOTENAME(name) + ' TO ' + QUOTENAME(@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))
/*
(No column name)
GRANT EXECUTE ON [uspPrintError] TO [RoleX]
GRANT EXECUTE ON [uspLogError] TO [RoleX]
GRANT EXECUTE ON [sprocTitleSearch] TO [RoleX]
GRANT EXECUTE ON [uspDBreindex] TO [RoleX]
GRANT EXECUTE ON [uspSuppliersByLocation] TO [RoleX]
GRANT EXECUTE ON [sprocSuppliersByLocation] TO [RoleX]
GRANT EXECUTE ON [uspGetBillOfMaterials] TO [RoleX]
GRANT EXECUTE ON [uspGetEmployeeManagers] TO [RoleX]
GRANT EXECUTE ON [uspGetManagerEmployees] TO [RoleX]
GRANT EXECUTE ON [uspGetWhereUsedProductID] TO [RoleX]
GRANT EXECUTE ON [uspUpdateEmployeeHireInfo] TO [RoleX]
GRANT EXECUTE ON [uspUpdateEmployeeLogin] TO [RoleX]
GRANT EXECUTE ON [uspUpdateEmployeePersonalInfo] TO [RoleX]
*/
Related articles:
Granting execute permissions to all stored procedures in a database
GRANT System Object Permissions (Transact-SQL)
|
