I have a SQL Server 2012 instance, and an Active Directory group, which I can add Active Directory users to. I have my personal AD account (MyAccount) and a service account (ServiceAccount) in that group.

I need to grant admin access to this AD group, for the whole instance. This means that I won't need to execute grant commands to every new table and database created. Any user on that group will automatically be able to create, drop, insert, select, etc.

Even trickier. I must be able to use Windows Authentication to login with MyAccount (I login on Windows and then login to SQL Server without needing to type username-password again), and login using ServiceAccount by typing username and password. I can't be forced to login on Windows with ServiceAccount (or run SSMS/SSDT/etc with that account) to be able to login on MSSQL.

What's the best practice for configuring that?

up vote 4 down vote accepted

Some of what you are asking for is actually pretty simple.

Permissions for your AD Group

You create a login for your AD group

CREATE LOGIN [domain\AD Group] FROM WINDOWS

Then grant it the access you want. You say you want to grant it Admin access but unless this is your DBA team I wouldn't add the AD Group to something like sysadmin. Instead grant them the permissions you actually want them to have. It sounds like you want

  • db_ddladmin - Role that grants permissions to create/modify objects within a DB.
  • db_datareader - Role that grants permissions to run a SELECT against any table/view within the DB
  • db_datawriter - Role that grants permissions to run UPDATE, INSERT or DELETE statements against any table/view in the DB.
  • EXECUTE - Permission that grants the ability to EXECUTE any stored procedure or function within the database.

To add this run the following on each user database.

CREATE USER [domain\AD Group] LOGIN [domain\AD Group] 
ALTER ROLE db_ddladmin ADD MEMBER [domain\AD Group]
ALTER ROLE db_datareader ADD MEMBER [domain\AD Group]
ALTER ROLE db_datawriter ADD MEMBER [domain\AD Group]
GRANT EXECUTE TO [domain\AD Group]

If you want these permissions added to new user databases then run the script in the model database as well.

Connecting using your service account

If you have an application running under your service account then when that application connects using trusted authentication it will connect to SQL using that service account. If however you want to connect to SQL using something like SSMS then you have two choices. You can either log into a machine using the service account or run SSMS as a different user.

To run SSMS under a different user hold down the SHIFT key and right click on your shortcut to SSMS and select Run as different user

enter image description here

You'll then get a login/password prompt for an AD login. Type in your service account name & password. Once SSMS opens any trusted (windows auth) connections will be made using the AD login you connected under (your serviced account in this case).

  • What O/S are you running? on my local machine I don't see "Run as different user" on the context menu for SSMS, either on the start menu, or on a shortcut created on the desktop. I do see "Run as administrator". – Max Vernon Apr 4 '16 at 15:18
  • @MaxVernon Are you hitting the shift key as you right click on the shortcut? "Run as administrator" is something completely different. Currently I'm running Win 7, but I've done this on both XP & Win 8 & 10. – Kenneth Fisher Apr 4 '16 at 15:40
  • ahhhhhhhh.... shift key. THANKS!!!! – Max Vernon Apr 4 '16 at 15:40
  • 1
    Thanks a lot!! I've done that and in initial tests it's working! Just for completeness, the code for add a group in sysadmin is ALTER SERVER ROLE sysadmin ADD MEMBER [domain\group]. – Hikari Apr 5 '16 at 13:29
  • 1
    I have used the connection string Server=myServerAddress;Database=myDataBase;Trusted_Connection=True; and it worked. I tried both executing the exe with my personal account and with the service account and both worked. I already configured MSSQL permissions for the AD group as explained in this answer and both accounts are in that group. Thinks working beautifully! – Hikari Apr 5 '16 at 14:40

You can grant admin access using this code:

CREATE LOGIN [DOMAIN\ADGroupName] FROM WINDOWS;
GRANT CONTROL SERVER TO [DOMAIN\ADGroupName];

That will allow any user who is a member of [DOMAIN\ADGroupName] to login to SQL Server without specifying a username/password, and will grant those users admin access to the entire instance, including all databases.

You can use the command-line applet, "runas" to start SQL Server Management Studio "as" the Service Account. The syntax for that would be (from a command-line):

runas /user:<service_account_name> C:\the\path\to\SSMS.exe

Granting CONTROL SERVER rights to a group or login in this way allows you to also DENY access to particular objects or databases. For instance, you could DENY ALTER ON DATABASE::master TO [DOMAIN\ADGroupName]; to prevent those users from making changes to master, while still allowing them to create and drop other databases, tables, etc. Be aware that a user with CONTROL SERVER rights could easily create a SQL login, grant that login sysadmin access, and have complete access to everything in SQL Server, including uncontrolled access to master. This is generally considered a pretty sizable security risk, and should likely only ever be used on non-production boxes.

I use the most excellent SQL Server permissions hierarchy poster to see a high-level-view of the implications of particular permissions.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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