How to allow domain groups access to SQL server?
I'm sure this is a simple problem, bu I've looked for days...
I have a SQL server that is part of a domain. The domain has many many users.
I want to allow select permissions to anyone within a particular group on the domain.
Right now I have to add a SQL user for every domain user, I'm sure there is a better way?
CREATE USER [Bob] FOR LOGIN [Domain\Bob]
GRANT SELECT ON TABLE TO "Domain\Group"
bob is a member of group
Transact-SQL:
use master ;
create login [DOMAIN\Group] from windows ;
use <database> ;
create user [DOMAIN\Group] from login [DOMAIN\Group] ;
grant select on object:: <schema>.<object> to [DOMAIN\Group] ;
You can create a SQL Login and a Database User from a Windows group. Note that Domain Users still connect as their user account - the SUSER_SNAME() function will return DOMAIN\user (not DOMAIN\Group).
Create a security group in ADUC.
Add the users to the group.
Create a SQL login for the group.
Map the SQL login to the appropriate SQL roles, databases, permissions, etc.