Home / server / Questions / 267619
Accepted
Kenny Bones
Asked: 2011-05-09 22:59:49 +0800 CST

Connect to SQL Server using Windows Group Authentication

  • 772

I've got this SQL Server 2005 Express installation that's got a Database I'm trying to connect to through a software that gives me the error "cannot connect using the user 'mydoimain\myuser'. Meaning, it's trying to connect using my domain user as a default. Which is ok. This is actually what I want. But I want to be able to add users to a group and grand that group permissions to the database instead of specifying each user.

So, I went ahead and created an AD group that I've added myself to. Then I added this AD group in Microsoft SQL Server Management Studio under Security/Logins using just one snippet of SQL Query code:

USE [myDatabase]
GO
CREATE LOGIN [myDomain\Groupname] FROM WINDOWS 
WITH 
   DEFAULT_DATABASE=[MYSQLDatabase];

This added the group under the Security tab. But I still get this error when trying to connect using the software from my client. What else do I need to set? I even tried to click on the newly added group that appeared after running the above query snippet and added every server roles as well. The user has been granted access to the database engine(done by default) and Enabled under Login (done by default).

What am I missing here?

sql-server sql-server-2005 sql-server-2008 permissions

1 Answers

  1. Best Answer

    You've created the login, which tells the server who you are. Now, you need to tell the individual databases who you are and what rights you should get. You'll do this by adding a user to individual databases and then adding rights to the user. Something like:

    use [db]
go
create user [myDomain\Groupname] for login [myDomain\Groupname]
go
grant select on schema::dbo to [myDomain\Groupname]
go

    That will add the user to the database "db" and give it read permission on all objects in the dbo schema.

    • 1