Knowledgebase

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