• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 754
  • Last Modified:

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 ?
1 Solution
Joe WoodhousePrincipal 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...

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now