October 10, 2008 at 7:26 pm
This afternoon we moved our database from one Windows 2003 Server to another one. What we did was a full backup from the old server and restore to the new server. The problem was the SQL user account didn't access anything. In the heat of the battle we weren't sure what happened, and so the system administrator deleted the SQL user account and recreated it. Plus he did a lot of other things which I don't understand.
What I want to know is, would using the system SP sp_change_users_login have fixed this problem, or no?
Rod
October 11, 2008 at 2:09 pm
it certainly sounds like an orphaned user problem, so probably yes.
Connecting to the database is a two level thing, with the login in the master database to determine the ability to connect to the instance, and the user in the database to determine permissions in the database. these need to be linked together by the SID value, and sp_change_users_login will do that for you if the login has not been transferred by a method that maintains the SID.
Possibly also the login had not been transferred at all, as just copying the database over does not bring the logins over.
---------------------------------------------------------------------
October 11, 2008 at 3:01 pm
Thank you, George. In the "heat of the battle" it is easy to forget the basics and just do things in a rush to get other things working.
Rod
October 11, 2008 at 3:12 pm
no problem, google the sp_help_revlogin stored procedure for best way to transfer over logins along with the users in the database whilst maintaining the SID values.
---------------------------------------------------------------------
October 11, 2008 at 3:24 pm
Did this fix things?
October 11, 2008 at 4:02 pm
Steve Jones - Editor (10/11/2008)
Did this fix things?
I won't really know until Monday when our users return to work. My tests indicate that the emergency deleting of the SQL user and re-creating it did help.
Rod
October 13, 2008 at 10:10 pm
OK Steve, and anyone else who is interested, it went well today (Monday). There were a few issues, but they were all related to ODBC connections for reports; nothing to do with the database migration.
I've learned an important lesson in all of this. It occurred to me that it is like a football game. The coach, players, etc all prepare, by going over what they play to do, but in my neophyte DBA's case, also how to prepare for trouble. And I suppose you don't have to prepare for all possible eventualities; it is sufficient to do some, trusting that will cover many of them. I suspect that it is sort of like the Pareto principle, where 80% of all troubles can be contributed to 20% of all causes. If you prepare for that 80% of troubles by addressing the 20% of possible causes, then the database migration would probably go well, at least in most cases. I should have thought of using the system SP sp_change_users_logins, which would have handled our backing up the databases from the old server and doing a restore to the new server. I won't forget that again.
Rod
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply