0

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?

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
3
  • Works like a charm! :) Just by curiosity, what was wrong with the above aproach?
    – Kenny Bones
    May 9, 2011 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, 2011 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, 2011 at 2:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .