moving users and permissions

Question

moving users and permissions

middletree

Hall of Fame

Points: 3316
More actions
September 11, 2009 at 2:54 pm

#214381

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

middletree Hall of Fame Points: 3316 More actions · Answer 1

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

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 2

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.

middletree Hall of Fame Points: 3316 More actions · Answer 3

Sadly, the detach/reattach thing was not an option this time. Long story.

I will try the SP. Thanks.

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 4

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.

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 5

Alternately you can use the scripts in the URL below to script your logins

http://support.microsoft.com/kb/246133

middletree Hall of Fame Points: 3316 More actions · Answer 6

I ran this:

exec sp_change_users_login 'Report';

and it returned nothing. Not sure what that means.

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 7

That means that you don't have any logins and users out of sync.

How did you move the objects?

middletree Hall of Fame Points: 3316 More actions · Answer 8

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.