Sql server 2005/2008 Add Login

Question

I want to have windows authentication login to SQL 2008, the problem I'm facing is that I want to have a User group to be a login, which i succeed in setting up a Login for the SQL database and a user for the database. But when I login with windows authentication it fails.

Example: I added a Login with windows authentication MyDomain\SqlUsers

MyDomain\Robert is in the MyDomain\SqlUsers User Group.

I login in(while logged in as MyDomain\Robert) to Sql managementstudio and it fails.

What am I doing wrong?

Answer 1

After you create the login you need to add the login to the databases.

What's the error message you get when logging in with MyDomain\Robert?

Answer 2

Did you set, in the Status tab of the Login properties, the 'Grant Permission' checkbox?

Answer 3

In the Login Properties there's a section titled "User mapping". You need to use this to grant your group access to the database(s) and specify what access they have.

Using a Windows group does work as I use this myself (along with millions of other SQL dbas :-)

JR