Sync Users After SQL 6.5 DB Restore
-
I have quite a few users in a 6.5 Database. After restoring the db to a dev box, I wonder if there is an easy way to sync up the users. Thank you for your help.
-
I may be mistaken, but I do not know of any easy way other than deleting the users that are in the database that were from the original server and then adding access for the users on your dev box.
A.J.
DBA with an attitude -
Howdy --
Depending on how experienced
, reckless 
, nuts 
, or desperate 
you are to get this done, you can use a technique I used to use for SQL6.5... but it's not for the squeamish... as you cannot get support from Microsoft on this... Some people get turned off by cracking open the system tables to fix something... and these days, more times than not, that's probably a safe piece of advice to live by... however, I was more daring in the days of SQL6.5...
That being said, if these are all SQL Logins (never tried it with the half-baked NT security stuff they had for SQL6.5), AND the logins already exist on the DEV server, you can attempt the following: (this is from memory, test it out before you do it)
- backup your master db, and/or export out/copy to another table the contents of the syslogins table in master to somewhere else
- restore your prod database to the dev server
- crack open the system tables (allow to make changes to system tables/catalogs)
- look at the sysusers table in your restored database. Now (from recollection, as we don't have any more SQL6.5 servers around), there was a sid column or suid column in there which corresponds to the suid or uid column in the master..syslogins table.
- try this for one user first....
- update the sysusers table in the restored database ONLY by setting the suid equal to the suid of that same id in the master..syslogins table. AT NO TIME DO YOU NEED TO TWIDDLE WITH THE SYSLOGINS TABLE ... but in case you do, that's why you have it backed up...
- they will now be re-associated... try logging in as that id. It should work... if not, then... have you restored this database previously to that dev server? if not, the dbid is out of whack and that will have to be reset too... but you can do that through the GUI...
- If it all works, continue on for the other ids.
- button up the system tables.
- that's it...
Hope this helps... good luck! -- MikeM
-
You can create the logins on the new server and then run the procedure sp_change_users_login which would map the user id's to logins.
-
sa24 --
Correct me if I'm wrong, but I don't believe that the sp_change_users_login sp existed in SQL6.5... I'm pretty sure of that... I even checked my dusty copy of my SQL6.5 BOL...
Would have been nice to have though...
-- Mike
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply