Server-wide Database Permissions in SQL Server

Question

We have a SQL Server 2014 installation, which is accessible by a subset of our Active Directory users via Windows Authentication.

Currently, when they log into the server for the first time they are given the sysadmin server role. I want to change this so that they belong to a more restrictive role.

I would ideally like them to be able to deal with data (SELECT, UPDATE, DELETE etc), EXEC stored procedures, and CREATE, ALTER and DROP tables, stored procedures and views.

The problem is that those permissions are database-level rather than server-level. Is it possible to have a server-level role that defines a common set of permissions for all databases on the server? If not, what's the best approach here?

• LowlyDBA - John M: Why not setup a security group in AD and give that login access to all the databases as needed?
• Steve Mangiameli: Are you sure you want them to have those permissions on ALL databases on the server? Most likely you want them to have those permissions on User DBs on the server and not system. In which case, you'd be best off creating a group and granting db_owner to each DB you want them to have access to. You may also want to have some gooooood auditing in place to see who is doing what.
• Paul: Thanks for the ideas. Will just add to db_owner on user databases.

Answer 1

So basically it sounds like you want to give this group of individuals db_owner access to all databases on the server. The way to do this is:

1. Create a local group account on the Windows Server (e.g. \\MYSERVER\SpecialGroup)
2. Add the AD accounts to that group.
3. Create a SQL Server login for \\MYSERVER\SpecialGroup
4. Then create a job, that runs every night (or however frequently you want) that loops through all your user databases and issues a CREATE USER statement for \\MySERVER\SpecialGroup and assigns them to db_owner.

• Jens D.: I would suggest not using db_owner. Otherwise your users can even drop the database and interfere with your backup!