Replicating data between databases

All of my databases reside on one Instance of SQL Server 2000.

I have seven distinct databases at my place of employment. Examples include a database for financial employees, sales employees, a database for our online employee incentive store, HR, etc...

The one thing each of these individual databases have in common is a "users" table that contains basic information about the user (address, state, zip, job title, etc). It also contains their login name and password.

I would like to set up the server so that when a user changes their information in one system (ex. financial), the SQL server updates the users record in every other database.

Just out of curiosity, I want to keep all of these databases clearly separated from one another, but it seems to be counterproductive to have a users table containing nearly the same info in each database (there are a few specific fields that differ from one database to another)...

Is there a better way to do this that I haven't thought of? Any help is much appreciated.

You know, I hate it when someone comes along and suggests something that you explicitly said you don't want to do in your original posting. That said...

Why don't you bring all of your tables from each database into one master database? You can bring in the common attributes for your users such as login name, mailing address and password, but retain specifics for each "system" they interface with by just increasing the number of columns in the table. In my experience, ANYTHING you can do to reduce redundant user information, logins and passwords, should be done. Not only is it annoying, it's also a security risk.

That being said, if you don't want to conjoin all your little databases into one central database, you might try to create yet another database and store only your user information in there. Although at this point it really feels like we are treating a "database" more like a table...

You could reference it like this:



Or, you could extend your existing table definitions to hold a datetime field for each record that would trigger on each insert or update. At the end of the night you could write a monstrous DTS package that would go thru and compare your user tables in each database and overwrite data of all "lesser" dates with the values of the "greater" date. Yuck.