How do I assign an entire Active Directory group security access in SQL Server 2008?

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.

"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
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

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

StackExchange Database Administrators Q#2572, answer score: 54