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?
db_owner
on user databases.