I would like to use integrated security with my internal application which is all on a domain. Unfortunately, I've never been able to get this to work well. I would like to assign an entire Exchange (Active Directory) Group a role in SQL Server for read/write access to certain tables. That way I wouldn't have to create an operator whenever someone is hired or delete an operator whenever someone is fired. Is this possible? What steps would I take to do this?

4 Answers 4

  • Set the AD group as a Login.
    "Login" means a SQL-Server-instance-level Login, not the AD concept of user/login.
    In SQL Server speak, this is a "Server Level Principal"
  • Create a mapped User in your database.
    You shouldn't really permission a User directly on tables.
    "User" means SQL Database User, not the AD concept of user.
    In SQL Server speak, this is a "Database Level Principal"
  • Add the User to a Role (also a "Database Level Principal")
  • GRANT permissions to the Role on the tables (tables, procs, etc are "securables")

Sample script:

USE master
USE mydb
EXEC sp_addrolemember 'rSupport', 'MYDOMAIN\APPLICATION SUPPORT'
ON Mytable
TO rSupport

sp_addrolemember is deprecated starting with SQL Server 2012, where ALTER ROLE should be used instead.

  • 3
    Would be helpful to explain why you should assign permissions to roles instead of SQL users (in this case, AD groups). Oct 16, 2015 at 18:07
  • Perhaps you mean "create user [...] for login [...]", instead of "from login"?
    – Agostino
    Oct 4, 2016 at 15:56
  • CREATE USER ... FOR LOGIN ...; and CREATE USER ... FROM LOGIN ...; both work for me (MSSQL2016). Not sure if there is a difference? Mar 16, 2018 at 16:40
  • No, both are valid learn.microsoft.com/en-us/sql/t-sql/statements/…
    – gbn
    Mar 16, 2018 at 22:13

From marc_s answering "How to add Active Directory user group as login in SQL Server":

In SQL Server Management Studio, go to Object Explorer > (your server) > Security > Logins and right-click New Login:

enter image description here

Then in the dialog box that pops up, pick the types of objects you want to see (Groups is disabled by default - check it!) and pick the location where you want to look for your objects (e.g. use Entire Directory) and then find your AD group.

enter image description here

You now have a regular SQL Server Login - just like when you create one for a single AD user. Give that new login the permissions on the databases it needs, and off you go!

Any member of that AD group can now login to SQL Server and use your database.

  • 2
    Linking to that SO answer is probably useful but you aren't adding anything new to the post, are you. You could have just posted the link as a comment.
    – Andriy M
    Mar 1, 2016 at 6:58
  • 2
    The answer has a nice screenshot for checking Groups under Object Type, since that's what I was missing when I was trying to do this. A link doesn't show it in as useful a way in my opinion. Just trying to help the next person...
    – Even Mien
    Mar 2, 2016 at 20:37
  • What do you mean? Usually a link is there to follow it, and everyone following the link would see the nice screenshot you are talking about, as well as everything else you've re-posted here. Not going to argue, though, just letting you know that my opinion is unchanged: a link (in a comment) would do just as well.
    – Andriy M
    Mar 2, 2016 at 20:47
  • 7
    My opinion is also unchanged. A link in a comment does not convey the same information as an actual image on a web page. "Show, don't tell."
    – Even Mien
    Mar 2, 2016 at 22:54

Granting the permissions within SQL Server to an AD Group is relatively straightforward. It can be done either through T-SQL or Management Studio.

For instance, if you have an AD group called MYDOMAIN\APPLICATION SUPPORT, you would create the login at the server level,and then use mappings to individual databases to give slightly more granular permissions such as data reader.

Ideally, all application access should be through stored procedures*, so only execute permissions on those stored procedures in that database is required.

* From a security point of view, to allow a particular user to see some specific data, you could create a procedure and grant the user execute permission on that procedure, and nothing else. Allowing the user to query directly would mean giving select permission on all the tables involved. It's also easier to work with procedures, and easier to debug.

Stored procedures abstract table access away and limit access. To DBA types, it's like "let me see all your instance variables: I don't want to use methods, getters or setters".


If the user is a member of a DOMAIN\SecurityGroup that has Sysadmin authority in SQL, then that will be used when accessing databases. Otherwise you need to look at what DOMAIN\SecurityGroup(s) have ben given authority in each database. If the user is a member of 2 SecurityGroups, with SecGroupA having select authority and SecGroupB having insert authority, then the user can select and insert.

Your Answer

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

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