SQL Server Performance Forum – Threads Archive
Restore of DB with bad login
We’re having a bit of a problem here and I’m hoping some of you can help me out. Our production database (used for OLTP) is a SQL 7 machine. We have another machine that we use for reporting which is a SQL 2000 machine. Originally, we were going to replicate the user databases from the production machine to the reporting machine. Eventually, however, our DBA decided to go a different route: we take the backups of the production database from an off-hour and restore the files to the reporting database. For a while this worked OK. However, of late, a problem seems to have occured. A login that we use for the database has become invalid whenever the restore is done to the 2000 machine. The only way to access that data through the login is to drop the login from all the databases and re-create it again with the same roles. There are no ownership problems here – the problem login does not own any objects in any database on either server. Does anyone have any ideas? I’d be grateful for any help you can provide. Thank you. Zev Steinhardtzev_steinhardt, You may want to consider DTS or visit this link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133 Jon M
This method of the data between the servers was not my choice — I would have preferred to use replication, but I was overruled. I doubt that they are going to allow me to redesign the process to incorporate DTS, but I will look into it. In the meantime, I’ll look at the link you provided. Thanks for your help. Zev Steinhardt
Run this script to resync the login on the affected dest database http://www.sql-server-performance.com/q&a27.asp
]]>