How do I sync sybase db logins from prod to test

Anyone can help how can I sync logins for sybase db in production to test db ?
Who is Participating?
Joe WoodhouseConnect With a Mentor Principal ConsultantCommented:
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...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.