September 11, 2009 at 2:54 pm
I'm finalizing a move of a database from win2k/sql2k to win2003/sql2005. It's been long and laborious. Had to do it piece by piece, for reasons too long to go into here. I wish I had been able to simply detach the database and reattach on the new server. I wish I had been able to use redgate. I mention this because when you read the question below, you will no doubt want to ask why I didn't do those things.
Anyway, the tables, Sps, and views have all been moved over. It seems that the users and logins have, too. But I am getting some errors that indicate some permissions are not what they ought to be. So I have been going through one at a time, and not seeing differences, but it's likely I am missing something.
So here's my question: what is the easiest way, using SQL server tools or a trial version of Redgate, that I can make sure that all users/logins/roles/permissions on the old server are in place on the new one?
thanks
September 14, 2009 at 12:04 pm
I don't generally bump things, but since this was posted late on a Friday night and has no replies, I'm hoping that a Monday mid-day bump will help things.
thanks in advance
September 14, 2009 at 12:32 pm
All your user permissions and objects will get moved with an attach. The only thing that you might be missing is the logins. sp_change_users_login with the report option should help you determine what is missing.
September 14, 2009 at 12:53 pm
Sadly, the detach/reattach thing was not an option this time. Long story.
I will try the SP. Thanks.
September 14, 2009 at 12:59 pm
Sorry, if you didn't detach/attach, how did you move the objects?
If you copied them over, the permissions aren't there. You can use the Generate script function of SSMS to move permissions to the new server.
September 14, 2009 at 1:02 pm
Alternately you can use the scripts in the URL below to script your logins
September 14, 2009 at 1:06 pm
I ran this:
exec sp_change_users_login 'Report';
and it returned nothing. Not sure what that means.
September 14, 2009 at 1:12 pm
That means that you don't have any logins and users out of sync.
How did you move the objects?
September 14, 2009 at 1:50 pm
Steve Jones - Editor (9/14/2009)
Sorry, if you didn't detach/attach, how did you move the objects?
Right-click>>Tasks>>Import.
If you copied them over, the permissions aren't there.
Thus my question.
You can use the Generate script function of SSMS to move permissions to the new server.
I don't see how that's done. Been poking all around SSM for 2 weeks on this thing.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply