Slava Murygin walks us through rights assignment with roles:
Problem description:
1. Need to create a group/user “User1”, which has to have only CRUD (Create-Read-Update-Delete) permissions for data in schema called “Schema1”.
2. Need to create a group/user “User2”, which has to have similar permissions as “User1” and have to be able create Views/Procedures/Functions in schema called “Schema2”.
3. The group/user “User1” has to have Select/Execute permissions for all newly created objects in “Schema2”.Solution: Create a special database role for group/user “User2”.
Read on for sample scripts, including some tests to ensure we don’t over-grant rights.