SQL Server 2008 – How to Assign Active Directory Group Security Access

active-directoryroleSecuritysql serversql-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?

Best Answer

  • 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.

Related Question