Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers 3

up vote 15 down vote accepted
  • Set the AD group as a login. And "login" means server level login not the AD concept of user/login. In SQL Server speak, this is a server level principal
  • Create a mapped user in. You shouldn't really permission a user directly on tables. And "user" means database user not the AD concept of user: in SQL Server speak, this is a "database level principal"
  • Add user to role (also a "database level principal")
  • GRANT permissions to the roles on the tables (a table or proc etc is a "securable")

Sample script

USE master;
GO
CREATE LOGIN [MYDOMAIN\APPLICATION SUPPORT] FROM WINDOWS;
GO
USE mydb;
GO
CREATE USER [MYDOMAIN\APPLICATION SUPPORT] FROM LOGIN [MYDOMAIN\APPLICATION SUPPORT];
GO
CREATE ROLE rSupport;
GO
EXEC sp_addrolemember 'rSupport', 'MYDOMAIN\APPLICATION SUPPORT';
GO
GRANT SELECT, INSERT,UPDATE, etc ON Mytable TO rSupport;
GO
share|improve this answer
    
Nice, clear explanation. +1 –  BGM Apr 19 '13 at 21:10
add comment

Granting the permissions within SQL Server to an AD Group is relatively straightforward, and can be done either through TSQL or Management Studio. For instance, if you can an AD group called MYDOMAIN\APPLICATION SUPPORT you create the login at the sever level,and then you can use mapping into 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 all that is required.

share|improve this answer
1  
Just curious, why is using stored procedures ideal? I thought the recent updates to SQL Server removed any difference. –  Michael Hedgpeth May 7 '11 at 13:35
    
@Michael: let's speak from simple security point of view - for showing to a particular user some specific data(you create a procedure and grant that user execute permission for that procedure, and nothing else); while letting him do queries would mean you'd be forced to give Select permission to the user to all the tables involved. I'm not sure, but also granting permission to a view means granting permission to all the tables involved. So it's easier to work with procedures. Plus the easy debug.. –  Marian May 7 '11 at 13:58
2  
@Michael Hedgpeth: stored procs 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"... –  gbn May 9 '11 at 15:06
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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