Access rights on SQL Server 2012

Question

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?

Answer 1

GRANT ALTER ANY DATABASE TO [somelogin]; or adding the user to the db_creator server-level role, will allow that login to create and/or restore databases, however, that user will not be able to access that database unless the restored database already has a user with the same SID that has the appropriate access. See my answer here for more details.

There is no server-level role or right that allows a login access to any database without simultaneously allowing that login to modify server-level properties.

The typical setup for creating/restoring databases consists of having a sysadmin member do the restore, and modify access per the requirements for that specific database.

Technet has a link to an excellent poster that describes all the rights and permissions grantable inside SQL Server.