SQL Server Performance Forum – Threads Archive

logins transfer with log shipping !!

dear all,
i finally set up log shipping and got it working as far as data changes are concerned it is working just fine. but now i have come across a problem which i could not find the solution to. i have some users in my primary database which are also owning objects in database and have sql server authentication set with them. now when i take the back up of db from primary server and restore it on secondary server and then i look at the properties of those users and match them with properties of same users on primary sever they are different. so pls help me out and tell me how can i solve the problem and keep the users rights everything same on secondary as well. waiting for your inputs. appreciate your help.
thanks a ton.

DTS can help you and see the related KBAs and articles:
http://support.microsoft.com/kb/314515
http://support.microsoft.com/kb/321247
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog02.mspx 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.

hi Satya/Luis, Thank you for your inputs. I am sorry to bother you guys again but i am new to sql server to trying to learn things about it. now the links which you sent are very good but Satya i am using sql server management studio and when i go to management and then legacy and then DTS and try to create Transfer logins DTS package there is no option to create it. so how do i create the package there in Studio?
although i used sp_addlogin procedure and got it working for some users. but there is one problem specifically giving trouble. hope you guys can help me soon about it. i have two databases "XYZ" and "ABC" and the users for XYZ is XYZ and also it is db_owner for this database so for user XYZ is no problem. but there is a db_owner for database ABC called user ABC. also this user is schema owner. default database for user ABC is ABC and he has dbaccess to XYZ also. so when i go to security and then logins and the properties for login ABC after doing sp_addlogin(after getting the info from primary server) on secondary server. it still shows without login. it does show it is mapped to XYZ but the it does not get mapped to database ABC and does not show dbrole there as db_owner, user ABC and default schema ABC in primary server database in properties for login name ABC– database is XYZ, user name is ABC, default schema is ABC and dbrole for this database is archive_abc_role and also there is other mapping for login ABC– database is ABC, user name is ABC, default schema is ABC and dbrole for this database is db_owner. in secondary server database when i go and look in the properties for login name ABC — first it is without login and under mappings — database is XYZ, user name is ABC, default schema is ABC and dbrole for this database is archive_abc_role but it does not show the mapping to database ABC. and if i try to do it manually it does not allow me because under the database the user ABC is there.
so this is the problem i am having. hope you guys will help me out as soon as possible. waiting for your inputs. appreciate your help.

finally on sql server 2005 i figured out to make the dts package and i did make the jobs to sync the login and then transfer the .dat file to shared folder and then i run the dts package but still the problem is that under login properties under user mappings it does show it is mapped to both the databases as described in my previous post. so please help me out where and what i am doing wrong. waiting for your inputs.
thanks a ton,
-jag

See thishttp://blogs.msdn.com/lcris/archive/2005/09/27/474505.aspx blog is any help in fixing outdated or out of sync logins on SQL servers. 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.

thank you satya. but i did check on both primary and secondary server the login names user names every thing match. i did match the sid also. i would really appreciate if you can create a login and assign him to 2 different databases as db_owner and then take the backup of the db and restore it to other place and then create this package for transfer login and see if it is showing the mapping there for both databases in login properties or not. and if you think it is working fine for you then i surely am doing something wrong some where. and if you also get the same problem may be you can find some solution to it. waiting for your feedback. sincerely appreciate your help.
thanks,
-jag

Are the logins created in same fashion betwen these 2 servers?
CHave you matched up the SIDs? 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.

yes satya the logins match and the sid is also matched. now finally i shifted the logins from primary to secondary. and the mapping is also ok now. i figured that when you run sp_resolve_logins i have to put both the db names there for dest_db. now one last query on this one i have is that lets say on primary server the password for user A is 5hrEaQ will it tranfer in the same fashoin or it has problems shifting the correct passwords. because now i have to go to secondary server and change the password other wise it is not allowing me to log in as it gives error about password and when i make the password as it is on primary server it works. so i may be missing some small piece here. waiting for your inputs. thanks.
-jag

Login transfer should copy the logins with same passwords with encryptions so you don’t need to change it…
But you can test the logins if you know the password… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

This puzzles me now, the procedure you have followed seems to be pretty similar to what has been referredhttp://support.microsoft.com/kb/246133 fyi As a last chance try using SSIS to transfer the logins between the servers. Use this script SET NOCOUNT ON
SELECT ‘EXEC sp_addlogin @loginame = ”’ + loginname + ””
,’, @defdb = ”’ + dbname + ””
,’, @deflanguage = ”’ + language + ””
,’, @encryptopt = ”skip_encryption”’
,’, @passwd =’
, cast(password AS varbinary(256))
,’, @sid =’
, sid
FROM syslogins
WHERE name NOT IN (‘sa’)
AND isntname = 0
.. from primary server and create on the secondary servr. 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.

well still it does not work for the passwords. if the password contains numbers,capital chars, normal chars and special char then it is not working. also i wanna know if lets say we donot have the db on secondary server and while starting log shipping we create the database then it does not create the database with the actual placings of data and log files meaning in different folders and different drives but it puts them in one drive all together. so i am taking the backup and restoring with option transaction logs need to be restored on secondary and then start log shipping. so is there a way that even if i create the db on secondary server using create db in log shipping config but with the placements of files and logs in identical fashion as it is in primary server and if there is then where and how to specify the folders for each data files and log files? also another question is that if i am creating db through log shipping way or just b/p and restore on secondary with recovery option then the db on secondary is in restoring mode. so then when we transer logins it does not create logins as it gives errors abt db not open. but then lets say we do it when the primary server is down then we cant do it because the whole primary server is down. so how to use DTS in that scenario? hope to hear from you soon. thanks

To transfer the logins from sql 2005 to sql 2005 use the following, it is working fine even pwd has special characters….. How to transfer the logins and the passwords between instances of SQL Server 2005
http://support.microsoft.com/kb/918992/ You can schedule a job to write the loginfo to a text using sqlcmd/osql by executing sp_help_revlogin procedure provided in the above article… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

thank you satya and mahmood. the login is fine now. but the last query abt this i have is, i wanna know if lets say we donot have the db on secondary server and while starting log shipping we create the database then it does not create the database with the actual placings of data and log files meaning in different folders and different drives but it puts them in one drive all together. so i am taking the backup and restoring with option transaction logs need to be restored on secondary and then start log shipping. so is there a way that even if i create the db on secondary server using create db in log shipping config but with the placements of files and logs in identical fashion as it is in primary server and if there is then where and how to specify the folders for each data files and log files? also another question is that if i am creating db through log shipping way or just b/p and restore on secondary with recovery option then the db on secondary is in restoring mode. so then when we transer logins it does not create logins as it gives errors abt db not open. but then lets say we do it when the primary server is down then we cant do it because the whole primary server is down. so how to use DTS in that scenario? and when we execute the dts from integration services in v. studio itself it does work fine but if i make a job and then run it from there it gives error the job is DTSRun /S PLF70EAP1 /E /N package. this is the erro i get in history. Provider Error: 14262 (37B6) Error string: The specified DTS Package (‘Name = ‘package’; ID.VersionID = {[not specified]}.{[not specified]}’) does not exist. Error source: Microsoft OLE DB Provider for SQL Server the package is saved by name package.dtsx under c:doc and settingsuser namemy docvisualstudio2005integration servicesprojectspackage.dtsx hope to hear from you soon. thanks
-jag

Reason for DTS packages is failing when it runs in a job is you saved the package as a file where as your command is looking in msdb..sysdtspackages80 table… You need to change the command to use dts saved as a file… When your primary server is down you can’t use DTS package or any menthod to copy the logins or any other information…So that you careate a job run the dts package to copy the logins info to a file so that it can be used when the server is down… or you can use sp_help_revlogin procedure to copy the login info to a file… You can configure the destination database to keep your data and log files to different location in one of the window of the log shipping configuration… Check the the following article…but is sql 2000 http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx</a> By the way my name is Mohammed [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] MohammedU. Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

i donot understand Mohammad, when i create this login tranfer package in business studio under integrated services. i save it in both places as it shows up under stored packages under filesystem and also under msdb, maintenance plans. so i created this package and then i imported on secondary server. and stopped the primary server and then i tried to execute it on secondary it does not work. so can you pls tell me what exactly you mean to put the output in file and then how to use that file on secondary server. hope to hear from you soon. thanks
-jag

]]>