newbie Simple Question about Users in SQL 2000

Hi,

I have attached to an MDF file through my SQL 2000 enterprise manager.
I fyou click on Users for this database in the MMC you see the
following:

Name Login Name DataBase Access
--------- ------------------
--------------------------
Administrator permit
d_xxxxx permit
dbo sa permit

When I run my web application through my com+ object I am getting the
following error

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'd_xxxxx'

The question is why there is no login name for d_xxxxx user? does it
have a significance? I am new to this and I do not see anywhere in
the web application where I can set the password.

Thanks

Re: newbie Simple Question about Users in SQL 2000

Mo (le_mo_mo@yahoo.com) writes:
>
> I have attached to an MDF file through my SQL 2000 enterprise manager.
> I fyou click on Users for this database in the MMC you see the
> following:
>
> Name Login Name DataBase Access
> --------- ------------------
> --------------------------
> Administrator permit
> d_xxxxx permit
> dbo sa permit
>
> When I run my web application through my com+ object I am getting the
> following error
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'd_xxxxx'
>
> The question is why there is no login name for d_xxxxx user? does it
> have a significance? I am new to this and I do not see anywhere in
> the web application where I can set the password.

In SQL Server there are principals (to use the terminology adopted in
SQL 2005) on two levels: server and database. A server principal is
also knowns as a login and a database principal is a user.

A login is what you need to connect to SQL Server. If there is no
login for you, you cannot connect. But just because there is a login
for you, does not mean you can access every database. Your login must
map to a user within the database you want to access.

As long as you work within a single server, this is not very tricky.
You create a login, and then a database user for that login.

But when you move databases between server, things get a bit complicated.
Let's say that on the target server there was a login Sture, and there
was a database where Sture was a user. Now you move that database to a
different server. How would Sture be able to access the database on the
new server? Obviously, he needs a login, so you create one. But he still
cannot access the database. Why? Because the mapping is not simply on
the name. In fact, SQL Server permits Sture to have the username
ErikXIV in a database if the DBA sees fit. Instead the mapping is on
something called SID. And typically SIDs are different from server to
server.

The way to address this situation is to use the stored procedure
sp_change_users_login, read about it in Books Online. But if there is
no login for d_xxxx you need to creat that first.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: newbie Simple Question about Users in SQL 2000

Thank you very much for your great post. It was quite informative. as
you mentioned I detached and copy the MDF file to the new machine and
attached it. I have read the documents and I am still a bit confused.
I have removed d_xxxxx from the users in the instance. Then I have
added d_xxxx in the logins in master. Then I have changed the d_xxxx
server roles to System Administrators and have assigned the database
access with the roles public and db_owner for the database. Is this
the wrong process? Currenlty under logins I have d_xxxxx as user and
under users in the instace I have:

Name Login Name DataBase Access
--------- ------------------ -------------------------
d_xxxx d_xxxx permit


I still get login failed for user d_xxxx. Can this be just a case of
bad password?

Thank you

Re: newbie Simple Question about Users in SQL 2000

Mo (le_mo_mo@yahoo.com) writes:
> Thank you very much for your great post. It was quite informative. as
> you mentioned I detached and copy the MDF file to the new machine and
> attached it. I have read the documents and I am still a bit confused.
> I have removed d_xxxxx from the users in the instance. Then I have
> added d_xxxx in the logins in master. Then I have changed the d_xxxx
> server roles to System Administrators and have assigned the database
> access with the roles public and db_owner for the database. Is this
> the wrong process? Currenlty under logins I have d_xxxxx as user and
> under users in the instace I have:
>
> Name Login Name DataBase Access
> --------- ------------------ -------------------------
> d_xxxx d_xxxx permit
>
>
> I still get login failed for user d_xxxx. Can this be just a case of
> bad password?

If the message is just "Login failed for user 'd_xxxxx'" this usually
indicates a problem with the password - or the login name itself. If
the problem was with the access to the database, the message is different.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx