Trigger firing in one db, accessing table in another db, another serve

[xp8103]

Source db is on a SQL 2k platform, running in SQL 7.0 compatibility mode. Destination is a SQL 2k5 db. Source db does not use Window authentication (I didn't set it up) but the app it backs up handles it with local logins. Destination db also has it's own local logins (nope, didn't set that one up either, both are from outside vendors.) Logins not the same. Need to establish authentication in source db triggers to access and update tables in destination. Have already set up linked server structure. Help?

 

[mrdenny]

The linked server is the hard part. Just setup a mapping within the linked server mapping the local account to an account on the remote server. You can use the other apps SQL account, but it's recommended to setup a seperate account for the linked server to use.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[xp8103]

mrdenny,
Understood. Problem is, there isn't just one account that could fire the trigger. Could be any user logged into the system under their individual local account?

 

[mrdenny]

Then you will want to use the global mapping option to map all users not specifically mapped to a single account on the remote server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog