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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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
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.
In SQL Server Management Studio, go to
Then in the dialog box that pops up, pick the types of objects you want to see (
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.