I'm having a problem querying data from two different databases on a SQL 2000 server. We have two databases, a main data db, and a separate security db that has login info.
Database logins for the data db DO NOT have access to the security db. I have a function that runs in the data db, but needs to query the security db. This works if the function runs from a user that has permissions in both databases (sa for example). But as a general user, it fails, because that general user login isn't a valid user in the security db. How can I do this?
Is there any way in SQL 2000 to "run as" another user? I just want to execute the subquery that goes to the security database as a user that has permissions to access that info. 2005 apparently has an "execute as" method, but that doesn't help me now. There's *got* to be some way to do this in 2000, right?? Thanks for any help!