I have a Microsoft SQL Server 2012 server used by two Active Directory groups.
How can I configure this groups so that:
group 1: Can do everything on database level, including read everything, write everything, add new databases, modify or delete them, and restore backups. However, the group can not change the settings of the server or shut it down.
group 2: Can read and write in every database but cannot modify its structure nor change server settings.
I've tried to add the groups to the model database, which works fine when creating new databases. However, if there are restores from other servers, the groups have no access to it.
Is there a way to define a server wide role or right, such that it applies to every database, regardless if the database was created or restored?