Blog on software engineering
How to grant exec rights to all stored procedures in Microsoft SQL Server 2005

How to grant exec rights to all stored procedures in Microsoft SQL Server 2005

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
end

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.