April 7, 2010 at 12:36 am
Hi experts,
I hafe configured replication from ASDB1 to ASDRF1, same DB name. Where the logins are same on both the servers.
Question1: After config trans replication, users are not able to connect the DB on second server though the same logins are on both the servers. Did i missed anything - mapping? If mapping how do i map 400 users?
Question2 : Do new users get replicated from ASDB1 to ASDRF1 when a new user is created on server ASDB1? Please help me at the earliest, how to proceed further.
DB users are same and Logins are same here on both the servers, servers are different.
fast help on this is appreciated.
- Win.
Cheers,
- Win.
" Have a great day "
April 7, 2010 at 2:11 am
It depends
Case 1 : If you rename the server Name for the existing replication you need to update the server Name in Object level.
Sp_addserver
Sp_dropserver
Case 2 : Even if you are trasfer logins from the Existing Server it is better to take that backup of the existing replication DB to have the UserId's (Db level) to map the logins in Server level.
Regards,
Vam 'C' :hehe:
April 7, 2010 at 2:13 am
Case 3: To fix the orfhaned logind use the store proc sp_change_users_login
Regards,
Vam 'c':hehe:
April 7, 2010 at 2:20 am
No Orphan users - tried that earlier...
Server name is different. We changed that in the application. Bu the same application is working fine for Server 1. But for server 2 it throws error as connection failed.
So what Iam trying to understand is the DB users and the SQL Logins which are same on both servers are unable to connect on the subscriber. Why this happens?
Is there any way to map the users to existing logins... script or any T-SQL statement to map the users for existing logins...
-Win.
Cheers,
- Win.
" Have a great day "
April 7, 2010 at 2:42 am
Thats what the issue is with...
Is there any way to map the users from DB level to Logins in Server level...
Cheers,
- Win.
" Have a great day "
April 7, 2010 at 10:08 am
I don't think I have understood why would replication cause any issues with SQL logins. Did you take a backup of publisher and restore it on subscriber while creating subscription?
The only way to restore the mapping between the database-level users and server-level logins is through the SID (security identifiers). If <database name>..sysusers and sys.syslogins reflect different SIDs for the same user/login name, then obviously the user-login mapping is in trouble. Check to see if the SID information is correct.
April 8, 2010 at 2:03 am
Yes,
DB backup is restored and then replication is configured.
Could you please elaborate how to check the SIDs and how it can effect the logins.?
Cheers,
- Win.
" Have a great day "
April 8, 2010 at 4:27 am
http://support.microsoft.com/kb/918992
Use EXEC sp_help_revlogin provided by Microsoft in the above link
April 8, 2010 at 7:07 am
Thanks Suresh for post.
I tried the same... But didnt clicked on my env.
There is a simple thing which hanged. Let me post here so that every one can check and help me to fix, every suggestion will rout to fix.
Every Login name has its user along with the same schema name.
Ex: Login : ASMBD1
User : ASMBD1
Schema : ASMBD1
DB : ASMBD1
This is what the main concern is with. Coming to replication there are some mismatching with the user accounts with the existing login with the db schema for every login.
What Iam trying to do is:
I need to script and to generate Login, with user , schema, password with permissions on databases.
If i can create this (the above script generation), then i can drop all the logins, users, schema, permissions on replicated db and can run the script once to get all the needy active.
i donno if am cleared the issue, but its a bit problematic....
Any suggestions are welcome and correct me if am wrong.....
Cheers,
- Win.
" Have a great day "
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply