March 24, 2009 at 9:24 am
What kind of authentication is being used (Nt or SQL) if NT authentication used then you don't have to worry.
March 24, 2009 at 9:33 am
we are using SQL auth 🙁
March 24, 2009 at 9:35 am
rishgup (3/24/2009)
we are using SQL auth 🙁
then sp_help_revlogin will solve your problem!
---------------------------------------------------------------------
March 24, 2009 at 9:44 am
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. :((
March 24, 2009 at 9:59 am
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.
---------------------------------------------------------------------
March 24, 2009 at 10:05 am
there are some users which I have to create and it is giving me
Windows NT user or group[....Server name....]not found. Check the name again
am totally confused now.
March 24, 2009 at 10:15 am
its a different domain (according to your previous posts) and a different server name I guess, thats probably why windows groups or users do not exist. this is what the error is telling you. check with whoever looks after Active Directory
---------------------------------------------------------------------
March 24, 2009 at 10:20 am
Thanks to All for your replies. I'll update you if I come with any automatic solution. otherwise I'll run sp_change_suers_login Command manually.
Thanks
Rishabh Gupta
March 24, 2009 at 11:15 am
george sibbald (3/24/2009)
rishgup (3/24/2009)
we are using SQL auth 🙁then sp_help_revlogin will solve your problem!
No sp_help_revlogin is useful for Transferring logins NOT to remap them! Which is what the PO asked.
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.
* Noel
March 24, 2009 at 11:22 am
Have you tried Synchronous mirroring (Full Safety) using a Witness Server which brings the mirrored server online in case of a issue?
March 24, 2009 at 11:25 am
unfortunately Client is not paying for Witness server.
March 24, 2009 at 12:34 pm
noeld (3/24/2009)
george sibbald (3/24/2009)
rishgup (3/24/2009)
we are using SQL auth 🙁then sp_help_revlogin will solve your problem!
No sp_help_revlogin is useful for Transferring logins NOT to remap them! Which is what the PO asked.
I am going to have to disagree with you there Noel. the OP has said he wants to avoid remapping orphaned ids, to do that he is going to have to go back and set up the logins via sp_help_revlogin. This is why I said it will solve his problem as he is using SQL authenticated logins. The post needs to be taken in context with previous posts.
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
---------------------------------------------------------------------
March 24, 2009 at 2:42 pm
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
* Noel
March 24, 2009 at 3:01 pm
Perhaps write a trigger when you add login to server1 then fire to go create server2.
How many logins are you adding in a week.
If only a few just do it as part of maintenance add to primary then add to secondary.
March 24, 2009 at 3:27 pm
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.
---------------------------------------------------------------------
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply