Logins across Linked Servers

Hi.

I wish to be bale to call a proc on a linked server but i am having difficulty with logins. I have a sql 2000 server linked to a sql 2005 server. To be able to call the proc on 2005 a login for the user must exist on both 2000 and 2005, and the login on 2005 must have access to the db and 'grant execute' on the proc.

Is there a way to allow access to the 2005 db without having to perform maintenance of the logins to ensure they match?

I know i could just have 1 login on 2005 that all users on 2000 are mapped to but this means new users added would have to also be mapped in the future.

Is it possible to call the stored proc on 2000 by impersonating a login that exists on both servers? Something similar to 'EXECUTE AS LOGIN = 'linklogin' in 2005? This would mean all users on 2000 could impersonate this one login and after this ine login is set up no further maintenance is required.

Thanks

Ewen

Yes, you can set up a more or less "permanent" login (read more at the link below) but that's normally a very bad idea. What it forces you to do is to take a less granular approach to managing your security. The temptation is that you will grant far more permissions than necessary to too many people - which violates the principal of "least rights", where you only grant what is necessary for the time.

It is a bit of a pain to manage two accounts. But if you'll use Windows security, you won't have to track passwords. Once you've set up your object security, you're essentially done - and safe!

http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c08ppcsq.mspx