Sql-server – Server-wide Database Permissions in SQL Server

permissionssql serversql server 2014

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?

Best Answer

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.
Related Question