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..
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'