Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Login Incorrect message in SQL Authentication Mode

Author  Topic 

usmanshafiq
Starting Member

2 Posts

Posted - 2010-03-02 : 06:56:35
I m using windows XP with sp2 running sql server. I have an accounts related software that uses sql db. Few days ago, i accidently deleted my database from enterprise manager. So I attached 3 days old backup of the db from another PC running its own SQL server. Now everything is working fine for me in windows authentication mode, but when i choose sql authentication mode, my software gives error message saying "login incorrect".
And i really want to use sql authentication mode because i want a client to connect. Both Computers are in the same workgroup.
Client was able to connect to SQLSERVER before i accidently deleted my db.

OR is there any way to connect the client with sever using windows authentication mode..

Plzz Help!!
Usman

franco
Constraint Violating Yak Guru

255 Posts

Posted - 2010-03-02 : 08:30:31
That's normal because on your SQL Server box, in the table sysxlogins of the master database, you still have your login that cannot reference the user in the attached db because it's coming from another box.
Every sysxlogins in the master database have a System Identifier (SID) that is a varbinary (85).
This is where the link is made between the user database and the sql server login.

Havea look at sp_change_users_login stored procedure in BOL.

If you try the stored procedure sp_change_users_login 'report' you will have a list of your orphan logins.

If your orphans are few you can try the stored procedure:

sp_change_users_login 'Update one'

Example could be:
EXEC sp_change_users_login 'Update_One', 'db_user', 'SQL_Login'



Franco
Go to Top of Page

usmanshafiq
Starting Member

2 Posts

Posted - 2010-03-04 : 01:16:02
Sorry for replying late..
thanks for your reply franco.. but sp_change_users_login 'report' does not show me any anything as i reinstalled my windows and sql server too :(

usman
Go to Top of Page
   

- Advertisement -