Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Works like a charm! :) Just by curiosity, what was wrong with the above aproach? –  Kenny Bones May 9 '11 at 13:14
    
Glad it worked for you. To answer your question, security in SQL Server has layers. Granting access to the server doesn't (and shouldn't) mean that that login has access to all the resources in all the databases on the server. Consider a scenario where you're an ASP. If you put multiple clients' databases on one instance, you should have some way of preventing a given client from seeing and accessing other clients' databases. So, you need to explicitly add users to each database and confer rights to the user. Hope that makes sense. –  Ben Thul May 9 '11 at 16:31
    
@Kenny You can read about Authentication and Authorization from this link(you may need to sign up): searchsqlserver.techtarget.com/news/1102093/… –  DaniSQL May 10 '11 at 2:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.