March 24, 2009 at 3:36 pm
george sibbald (3/24/2009)
noeld (3/24/2009)
IF and only IF there is only one database you can drop all SQL logins on the destination Server and copy the logins from the Primary using sp_help_revlogin. Then there is no need for remapping.
Agreed. This is what I have suggested he do as the logins have already been created by some other means and have a different SID. The OP will need to determine which logins need re-creating.
cheers
george
That ONLY works if you are using ONE database.
If you are using more than one, the remap is the way to go because you can have different UID on each DB for the same login ID.
That is why sp_help_revlogin could not work even after deleting the existing logins.
Hope is clear
don't see that at all, UNLESS one database is copied from the primary server and the other database was created on the secondary, then the same user will have different ids in the two databases and you will have to remap one or the other. We have no evidence that is the situation here.
If a login is created and given access to more than one database, the userids all point back to the same login and the SIDs match. So if you copy over both databases to a different server, and then use sp_help_revlogin to copy and create the login, no remapping will be required.
I am not happy the OP has to resort to dropping logins, but I see no way out of it if he truly wants to avoid sp_change_users_login. Otherwise bite the bullet and script up the remapping of all orphaned users.
Assume for a minute that there are OTHER databases already created on that destination server.
What do you do? DROP the logins? I guess not.
Assume that the server is target of several sources
What do you do? Drop the logins? I guess not.
Assume that the all dbs come from the same source AND no other DBs are already in the target THEN your proposition is valid!
* Noel
March 24, 2009 at 4:03 pm
Noel, we are on different wavelengths. see this post bottom of page 2 (for me)
rishgup (3/24/2009)
--------------------------------------------------------------------------------
I ran sp_help_revlogin on Primary server and copy script and run onto Secondary server.
It gives error that SPID is in use. and when I change query from Create login to Alter login it gives Incorrect syntax near 'SID' error. (
Do you mean ID in use? It would do if login already exists. You are going to have to drop these logins first. sp_help_revlogin is for copying logins to a new server up front rather than amending logins that already exist.
I have never suggested the OP drop all logins, in fact in a previous post I have said the OP will need to determine what logins to re-create. What else is on the server will go towards determining that.
Can we call it quits now? There are enough warnings and caveats and extra info in this thread for anyone coming along behind us. 🙂
---------------------------------------------------------------------
March 24, 2009 at 4:14 pm
Can we call it quits now? There are enough warnings and caveats and extra info in this thread for anyone coming along behind us. 🙂
Quit :hehe::-D
* Noel
March 24, 2009 at 6:24 pm
Gents,
I really APpreciate your suggestions. I'll update you all if I anything works out for me.
Rishabh
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply