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

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

Login question

I have a login that I need to use across multiple servers in a thin
client application. It's for internal use so one login will suffice. I
initially thought I could just make a login on our domain and then add
this user to each server as a db owner of the respective databases.
However, I can't use the login in a connection string (as far as I
know) as I can't pass down the username/password as connection strings
can't take domain logins.
Is there a way around this? Or should I just create the user on each of
the multiple SQL servers, and then if the password changes sync it
accross manually?> However, I can't use the login in a connection string (as far as I
> know) as I can't pass down the username/password as connection strings
> can't take domain logins.
When you use Windows logins, the login is validated by Windows, you don't pass this in the
connection string, all you say is to ask for a Trusted or a Windows login.
But I'm sure you don't want all end-users to share the same account in Windows...? So, create a SQL
Server login instead.
Or you might want to read about "application roles", which allow each user to have its own login,
but still have one user (the application role) in the database. This way, you don't lose
traceability regarding who did what.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<sloppycode@.gmail.com> wrote in message
news:1145437475.402577.130300@.i40g2000cwc.googlegroups.com...
>I have a login that I need to use across multiple servers in a thin
> client application. It's for internal use so one login will suffice. I
> initially thought I could just make a login on our domain and then add
> this user to each server as a db owner of the respective databases.
> However, I can't use the login in a connection string (as far as I
> know) as I can't pass down the username/password as connection strings
> can't take domain logins.
> Is there a way around this? Or should I just create the user on each of
> the multiple SQL servers, and then if the password changes sync it
> accross manually?
>

Login question

I have a login that I need to use across multiple servers in a thin
client application. It's for internal use so one login will suffice. I
initially thought I could just make a login on our domain and then add
this user to each server as a db owner of the respective databases.
However, I can't use the login in a connection string (as far as I
know) as I can't pass down the username/password as connection strings
can't take domain logins.
Is there a way around this? Or should I just create the user on each of
the multiple SQL servers, and then if the password changes sync it
accross manually?> However, I can't use the login in a connection string (as far as I
> know) as I can't pass down the username/password as connection strings
> can't take domain logins.
When you use Windows logins, the login is validated by Windows, you don't pa
ss this in the
connection string, all you say is to ask for a Trusted or a Windows login.
But I'm sure you don't want all end-users to share the same account in Windo
ws...? So, create a SQL
Server login instead.
Or you might want to read about "application roles", which allow each user t
o have its own login,
but still have one user (the application role) in the database. This way, yo
u don't lose
traceability regarding who did what.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<sloppycode@.gmail.com> wrote in message
news:1145437475.402577.130300@.i40g2000cwc.googlegroups.com...
>I have a login that I need to use across multiple servers in a thin
> client application. It's for internal use so one login will suffice. I
> initially thought I could just make a login on our domain and then add
> this user to each server as a db owner of the respective databases.
> However, I can't use the login in a connection string (as far as I
> know) as I can't pass down the username/password as connection strings
> can't take domain logins.
> Is there a way around this? Or should I just create the user on each of
> the multiple SQL servers, and then if the password changes sync it
> accross manually?
>