4

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

stuck
  • 687
  • 2
  • 10
  • 23

2 Answers2

6

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

Greenstone Walker
  • 779
  • 1
  • 5
  • 16
1

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.

joeqwerty
  • 109,901
  • 6
  • 81
  • 172