Change connected user to avoid owner prefix in queries

Hi everybody,

I've five instances of SQL Server 2000 with the SAME database with a
DIFFERENT owner in each server. I, as the administrator, have a lot of
queries that I have to execute in some or all servers. The problem is
that I have to connect to all servers with MY user, not each of the db
owners...

So I have queries this way:

select * from mike.table1 t1 join mike.table2 t2 on...

And when I connect to another server I have to change mike for jeremy
in all the SQLs...

And when I connect to another server I have to change jeremy for nina
in all the SQLs...

I know that there was an old, v7, deprecated way to change the
"schema", something like

change current user to kimberly
go
select * from table1 t1 join table2 t2 on...

This way, I'll change ONLY once the connected user. I could even do at
the beginning of the script an IF, to change the connected user
depending on @.@.SERVERNAME !!!

Can someone remember this instruction?

Thanks in advance for your help !!!Found it !!!

setuser 'q01'

-- quien importo una orden de transporte
select USERNAME, SYDATE, SYTIME, CLIENT
from TPLOG
where CMDSTRING like '%D02K909789%

Quote:

Originally Posted by

>From BOL:

SETUSER
Allows a member of the sysadmin fixed server role or db_owner fixed
database role to impersonate another user.

Important SETUSER is included in Microsoft? SQL Server? 2000 only for
backward compatibility, and its usage is not recommended. SETUSER may
not be supported in a future release of SQL Server.

Syntax
SETUSER [ 'username' [ WITH NORESET ] ]

Arguments
'username'

Is the name of a SQL Server or Microsoft Windows NT? user in the
current database that is impersonated. When username is not specified,
the original identity of the system administrator or database owner
impersonating the user is reestablished.

WITH NORESET

Specifies that subsequent SETUSER statements (with no specified
username) do not reset to the system administrator or database owner.

CDatabase::OpenEx( "ODBC; ...") question

CDatabase::GetConnect() returns "ODBC; ..."
However, CDatabase::OpenEx() may not include "ODBC;". It will fail to
connect if it has that prefix.
So you can not simply do:
{
CDatabase a, b;
a.OpenEx("..."); // assume this connects fine.
// try another connection to the same.
b.OpenEx( a.GetConnect() ); // fails: Data source name not found and no
default driver specified
}
I must remove the "ODBC;" prefix.. Why this intolerance? This is cumbersome!
This means I can not simply store GetConnect() string to resource and open a
new DB connection using it.
I guess I can use Open() instead of OpenEx() but that sucks too.Xref: TK2MSFTNGP08.phx.gbl microsoft.public.vc.mfc:407326 microsoft.public.s
qlserver.odbc:41712
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:Ohmp0if2DHA.2528@.TK2MSFTNGP10.phx.gbl...

quote:

---

> CDatabase::GetConnect() returns "ODBC; ..."
> However, CDatabase::OpenEx() may not include "ODBC;". It will fail to
> connect if it has that prefix.
> So you can not simply do:
> {
> CDatabase a, b;
> a.OpenEx("..."); // assume this connects fine.
> // try another connection to the same.
> b.OpenEx( a.GetConnect() ); // fails: Data source name not found and

---

no

quote:

---

> default driver specified
> }
> I must remove the "ODBC;" prefix.. Why this intolerance? This is

---

cumbersome!

quote:

---

> This means I can not simply store GetConnect() string to resource and open

---

a

quote:

---

> new DB connection using it.
> I guess I can use Open() instead of OpenEx() but that sucks too.

---

You could perhaps write a function that extracts the fields you want.
b.OpenEx( to_conn_str(a.GetConnect()) );
Tom.|||Yep, I did.. CDBString::MinimalizeODBC( s.GetConnect() );
Better yet, probably subclass CDatabase, override the OpenEx to just remove
the ODBC;
Thanks.
"TT (Tom Tempelaere)" <_N_OSPAMtiti____@.hotmail.comMAPSO_N_> wrote in
message news:Ei0Nb.11053$cQ5.3537008@.phobos.telenet-ops.be...

quote:

---

> "Lisa Pearlson" <no@.spam.plz> wrote in message
> news:Ohmp0if2DHA.2528@.TK2MSFTNGP10.phx.gbl...
> no
> cumbersome!
open[QUOTE]
> a
> You could perhaps write a function that extracts the fields you want.
> b.OpenEx( to_conn_str(a.GetConnect()) );
> Tom.
>

---