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?

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


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