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 (
EXEC stored procedures, 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?