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.
| Author |
Topic |
|
panthagani
Yak Posting Veteran
58 Posts |
 Posted - 2009-02-01 : 08:06:23
|
| I have a database called Main which used to reside on SQL Server 2000. Main DB has a user called 'TalismaAdmin'. The application needed to be moved to SQL Server 2005. Main DB was restored onto SQL server 2005. After this, whenever I run sp_tables_ex 'NU-DB-01' (NU-DB-01 is the name of SQL Server 2005) via SSMS, I get the following error:Msg 18456, Level 14, State 1, Line 1Login failed for user 'TalismaAdmin'.In the SQL ErrorLog, this error maps to State 8 (password mismatch). I reset the password of 'TalismaAdmin' several times and rebooted the server but it is still the same. The password of 'TalismaAdmin' on SQL Server 2000 was 'talisma'. After moving to SQL Server 2005, the client applied password policy ('Password must meet complexity requirements') on the domain controller to which NU-DB-01 talks to. On the client's request, the password was changed from 'talisma' to 'z0_t@li$ma08' on SQL 2005. The above behavior started after the password got updated. Other than this, everything else works fine. I can login to NU-DB-01 with TalismaAdmin new password, login to the app and a host of other things from the app side.Strange thing is, if I reset TalismaAdmin password back to 'talisma', connect to SSMS, and run sp_tables_ex 'NU-DB-01', it works perfectly fine and returns the catalog for the default database. But as per the requirement, the password cannot be reverted back. I need sp_tables_ex 'NU-DB-01' to work to support one particular functionality on my front end app.My question is: 'Is the old password cached somewhere by SQL Server 2005?' The Main DB got migrated from SQL 2000 to SQL 2005. I checked and the SIDs of 'TalismaAdmin' user and login. They match on both 2000 and 2005. They are different, nonetheless, they do match on each server.Has anyone seen this behavior before? Is there a known workaround? Please advise. Thank you. |
|
|
heavymind
Posting Yak Master
115 Posts |
Posted - 2009-02-02 : 08:02:16
|
| Hi. You are having orphaned users in the database. Read more at [url]http://db-staff.com/index.php/component/content/article/79-fix-orhaned-users[/url].run right after restore queryEXEC sp_change_users_login 'update_one', 'TalismaAdmin', 'TalismaAdmin'as I understand both username and login name are TalismaAdminThis should help. If it does not - runexec sp_change_users_login 'report'Thanks, VadymMCITP DBA 2005/2008Chief DBA at http://www.db-staff.com |
 |
|
|
panthagani
Yak Posting Veteran
58 Posts |
Posted - 2009-02-02 : 10:29:42
|
| Hi there, heavymind. I will run sp_change_users_login 'update_one', 'TalismaAdmin', 'TalismaAdmin' later today, after production hours and post an update. Thank you! |
|
|
|
panthagani
Yak Posting Veteran
58 Posts |
Posted - 2009-02-05 : 01:13:52
|
| I ran sp_change_users_login with both 'Auto_Fix' and 'update_one' options. No luck. Then I deleted the 'TalismaAdmin' user from each of the databases and recreated them. Still the same. The only thing I haven't tried is to delete and recreate the 'TalismaAdmin' login on the server. exec sp_change_users_login 'report' does not report any orphaned user on any database. Please advise. Thank you! |
|
|
|
|
|
|
|
|
sp_tables_ex returns <Login failed for user> Help!