Restricting database access to specific Windows groups in SQL Server 2008

Question

Hi,

I'm trying to restrict access to a database on my server to only allow users from a specific Windows group. I have enabled Windows authentication for the server, but it seems as if I can only allow access to specific users, not specific groups... is there any way I can do this? Most of the resources I've found contain SQL Server 2005 specific instructions, and I have thusfar been unable to translate them over to 2008.

Any help would be much appreciated. Thanks!

Answer 1

SQL Server 2005 and 2008 are essentially the same here.

USE master
CREATE USER [domain\group] FROM WINDOWS
GRANT CONNECT SQL TO [domain\group]

USE MyDB
CREATE USER [domain\group] FROM LOGIN [domain\group]
GRANT CONNECT TO [domain\group]

This will allow everyone in the windows group to connect to your database. Is that what you want?

Or do you want to deny some folk access, which I thought at first...

Answer 2

Do the following:
In the New user Dialog click the Search button.
Then click Object Types.
Select Groups, deselect Users and Built-in Security Principals
Click Ok.
Then click on Find Now

At this point you should see a list of groups available.
Pick the one you want and click OK.

Assign rights as necessary.