How do I sync sybase db logins from prod to test

Posted on 2011-10-20
Last Modified: 2012-05-12
Anyone can help how can I sync logins for sybase db in production to test db ?
Question by:motioneye
    1 Comment
    LVL 24

    Accepted Solution

    This is a more complex question than you might think. Are all the databases the same? (ie. dumping from Prod into Test?)

    The reason I'm asking is because it's simple to sync the logins... but unless you've also synced everything else, much will now be broken. For example users and aliases in databases relate back to the suid of a login. If you sync the logins without also syncing every user database, the users and aliases are now probably broken.

    So let's assume you're also refreshing every single user database, as that's the simpler scenario. Here all you need to do is to use bcp to transfer a number of system tables... but you must deal with the duplicates this will produce.

    At a minimum you should bcp master..syslogins, master..sysloginroles, and master..syssrvroles.

    In terms of duplicates, the easiest way to deal with them would be to drop all user-created roles and logins from Test (NOT any of the system created ones!!!), then either edit the bcp files to remove the (system) duplicates (such as the "sa" and "probe" logins, and the built-in roles), or create views with WHERE clauses to filter these out and bcp out from the views.

    This is the simplest scenario, and it basically means "make Test a 100% clone of Prod".

    If that's not what you want to do, it gets a lot more complicated, but I won't get into any of that unless you say (for example) that you want to sync Prod logins to Test without losing anything already in Test, or you want to keep all the Test passwords, etc. All of that is doable but a *lot* more complicated...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    If you're not part of the solution, you're part of the problem.   Tips on how to secure IoT devices, even the dumbest ones, so they can't be used as part of a DDoS botnet.  Use PRTG Network Monitor as one of the building blocks, to detect unusual…
    PRTG Network Monitor lets you monitor your bandwidth usage, so you know who is using up your bandwidth, and what they're using it for.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    This video discusses moving either the default database or any database to a new volume.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now