1

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?

Improve this question
3
  • 1
    Why not setup a security group in AD and give that login access to all the databases as needed?
    – LowlyDBA - John M
    Mar 6, 2015 at 17:16
  • 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.
    – Steve Mangiameli
    Mar 6, 2015 at 17:17
  • Thanks for the ideas. Will just add to db_owner on user databases.
    – Paul
    Mar 6, 2015 at 18:08

1 Answer 1

Reset to default
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.
Improve this answer
1
  • 2
    I would suggest not using db_owner. Otherwise your users can even drop the database and interfere with your backup!
    – Jens W.
    Mar 7, 2015 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.