How to grant exec rights to all stored procedures in Microsoft SQL Server 2005
June 9, 2009
0 Comments
Users are in our case always a member of some role. The role is given exec stored procedure rights and db_datareader, db_datawriter rights.
Users in the role “TestRole” can select, insert, update, delete data and execute storedprocedures and functions.
if not exists (select 1 from dbo.sysusers su (nolock) where su.issqlrole = 1 and su.[name] = 'TestRole') begin create role TestRole grant execute to TestRole exec sp_addrolemember db_datareader, PegasoServiceRole exec sp_addrolemember db_datawriter, PegasoServiceRole endTags: SQL Server