Sql-server – SQL Server Active Directory login groups with limitation

Individual accounts are going to be much harder to administer. They give you more granular control over access, but you can not keep out your Active Directory administrators. Anyone who is granted local administrator rights on your server can gain sysadmin access to the instance. And, as a very good DBA told me once, if you can't trust your domain admins you've got bigger problems on your hands.

By abstracting your access to AD groups, you gain two benefits:

1. Easier to manage access groups and rights based on roles, combined with stratified access control for other assets using those AD groups.
2. Provide more consistent auditing, as you will only need to show access by AD group and then who is contained within that group.

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

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).