how can i allow domain users to acces ms sql server 2005?

[aldi]

Hello all,

I'm playing with vs2005 and i'm trying to connect to ms sql 2005 to add a connection to the data source.

I selected the name of the ms sql server in the server name list, then selected "Use windows authentication", entered the database name (AdventureWorks) then click "Test connection", but I get the following error:

"Logon failed for user: domain\username"

I know that I have to give permission to domain user to access the database server, but don't know how.

Could anyone help please as to tell me where I give the rights or add the domain accounts to allow access to the database? hope is simple!

Thanks in advance,

Aldi

 

[paskuda]

First, create the login for a user.

CREATE LOGIN [Domain\User] FROM WINDOWS

If you want to give that user a fixed server role use:

EXECUTE sp_addsrvrolemember loginname, rolename

If you want to give access to database you have to create user in the database for the login and grant him a role (fixed one, or created by you)

CREATE USER username FOR LOGIN loginname
EXECUTE sp_addrolemember rolename, username

 

[aldi]

Thanks paskuda!

I followed your instructions and I can connect now.

Thanks again!