SQL Server Performance Forum – Threads Archive
Logins transfer problems.
Hi, I’ve setup Log Shipping between two SQL 2000 Servers Ent. (64 bit editions).Almost everything works fine with one exception.
I see that Log Shipping doesn’t transfers any logins to secondary server.
Example:
If I add new user on primary server he doesn’t appears on secondary server.
This is very strange, because it works fine on my testing servers (32 bit).
Any ideas?
Log shipping will not transfer the logins… Check the following… How to configure security for SQL Server log shipping
http://support.microsoft.com/kb/321247 Frequently asked questions – SQL Server 2000 – Log shipping
http://support.microsoft.com/kb/314515 How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/kb/246133
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=20594
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Hi, I understannd that I have to make DTS package to move users then bcp do create dat file with logins and resolve logins storep procedure. This is simple and i have no problem with this. My problem is that I can’t see any users (in users view in EM) in Log Shipped database on secondary server.
On my testing serwer when I add new user to primary server the same users appears on secondary server. Ofcourse SID is different.
Why it doesn work on my production server? I’ve found this in documentation. Maybe this is my main problem? "Important The SQL Server 2000 destination server cannot be running the 64-bit version of SQL Server 2000. DTS components for the 64-bit version of SQL Server 2000 are not available. If you are importing logins from an instance of SQL Server that is on a separate computer, your instance of SQL Server will must be running under a Domain Account to complete the task."
Users are part of the database and I believe you should see them in log shipped database when you add new user. Did you check through QA? select * from <dbname>..sysusers where name = <New user name>
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
It looks like logins exist now in database. I’ve setup Log Shipping one more time.
If I run your command or sp_change_users_login ‘report’ I can see logins.
Now I’m trying to make sid change but it doesn’t work. I have syslogins.dat file form my primary server (bcp copy).
I use command: use master
EXEC sp_resolve_logins
@dest_db = ‘HURT’,
@dest_path = ‘z:’,
@filename = ‘syslogins.dat’ After that i’m trying to compare SID’s between my primary and secondary servers I can see that SID’s are different.
One think… I didn’t change my secondary server to primary (role change).
I need secondary server for reporting and I want to use the same logins.
Maybe that’s why sp_resolve_logins doesn’t work?
Why don;t you run SP_CHANGE_USERS_LOGIN to see any mismatch of logins. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
I’ve run "sp_change_users_login" and I see all mismatched logins.
I can always add login manualy with specified sid using "sp_addlogin" but I wonder why "sp_resolve_logins" procedure doesn’t work?
http://vyaskn.tripod.com/troubleshooting_orphan_users.htm a good one to explain the scenario and fix.
KBAhttp://support.microsoft.com/kb/298758 fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>