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.

Certificate not found

Hello,

I have two different instances of sql server 2005 but i get

Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not found. State 89.

This is one of the two instances:

use master

--ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003'

create master key encryption by password = 'hello'

create certificate [Certificato2]

from file = 'c:\certs\TransportCert2.cer'

with private key (FILE='c:\certs\TransportCert2.pvk',

decryption by password='simone')

active for begin_dialog = ON

CREATE LOGIN [M02] WITH PASSWORD = 'wrPqYkr%bm3';

ALTER LOGIN [M02] DISABLE;

CREATE USER [M02] FROM LOGIN [M02];

GO

create certificate [Certificato1]

authorization [M02]

from file = 'c:\certs\TransportCert1.cer'

active for begin_dialog = ON

GO

USE PublisherdDB

--ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003'

create master key encryption by password = 'hello'

create certificate [CertificatoDialogo2]

from file = 'c:\certs\DialogCert2.cer'

with private key (FILE='c:\certs\DialogCert2.pvk',

decryption by password='simone')

active for begin_dialog = ON

CREATE USER [Proxy::IsDbLookupRequestServiceM02] WITHOUT LOGIN;

GO

create certificate [CertificatoDialogo1]

authorization [Proxy::IsDbLookupRequestServiceM02]

from file = 'c:\certs\DialogCert1.cer'

active for begin_dialog = ON

CREATE REMOTE SERVICE BINDING [RSB::IsDbLookupRequestServiceM02]

TO SERVICE 'IsDbLookupRequestServiceM02'

WITH USER = [Proxy::IsDbLookupRequestServiceM02],

ANONYMOUS = OFF;

GO

CREATE ROUTE [Route::IsDbLookupRequestServiceM02,D516E70B-59D6-4BF4-882A-BDA7ACD6EB07] WITH

SERVICE_NAME = 'IsDbLookupRequestServiceM02',

ADDRESS = 'tcp://PORTATILEXP:4022';

GO

GRANT SEND ON SERVICE::[IsDbLookupResponseService] TO [Proxy::IsDbLookupRequestServiceM02]

GO

USE MASTER

CREATE ENDPOINT [BROKER]

AUTHORIZATION [VIDEOSYSTEM\Simone_Farinea]

STATE=STARTED

AS TCP (LISTENER_PORT = 4033, LISTENER_IP = ALL)

FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED

, MESSAGE_FORWARD_SIZE = 10

, AUTHENTICATION = CERTIFICATE [Certificato2]

, ENCRYPTION = REQUIRED ALGORITHM RC4)

GRANT CONNECT ON ENDPOINT::[BROKER] TO [M02];

Here is the second one:

use master

--ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'hello'

create master key encryption by password = 'hello'

create certificate [Certificato1]

from file = 'c:\certs\TransportCert1.cer'

with private key (FILE='c:\certs\TransportCert1.pvk',

decryption by password='simone')

active for begin_dialog = ON

CREATE LOGIN [SIMONEX] WITH PASSWORD = 'wrPqYkr%bm3';

ALTER LOGIN [SIMONEX] DISABLE;

CREATE USER [SIMONEX] FROM LOGIN [SIMONEX];

GO

create certificate [Certificato2]

authorization [SIMONEX]

from file = 'c:\certs\TransportCert2.cer'

active for begin_dialog = ON

GO

USE vsi

--ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'hello'

create master key encryption by password = 'hello'

create certificate [CertificatoDialogo1]

from file = 'c:\certs\DialogCert1.cer'

with private key (FILE='c:\certs\DialogCert1.pvk',

decryption by password='simone')

active for begin_dialog = ON

CREATE USER [Proxy::IsDbLookupResponseService] WITHOUT LOGIN;

GO

create certificate [CertificatoDialogo2]

authorization [Proxy::IsDbLookupResponseService]

from file = 'c:\certs\DialogCert2.cer'

active for begin_dialog = ON

GRANT SEND ON SERVICE::[IsDbLookupRequestServiceM02] TO [Proxy::IsDbLookupResponseService]

GO

CREATE ROUTE [Route::IsDbLookupResponseService,88EB00C4-8CA9-4B45-9899-677AA70818B1] WITH

SERVICE_NAME = 'IsDbLookupResponseService',

ADDRESS = 'tcp://SIMONEX:4033';

GO

USE MASTER

CREATE ENDPOINT [BROKER]

AUTHORIZATION [VIDEOSYSTEM\Simone_Farinea]

STATE=STARTED

AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL)

FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED

, MESSAGE_FORWARD_SIZE = 10

, AUTHENTICATION = CERTIFICATE [Certificato1]

, ENCRYPTION = REQUIRED ALGORITHM RC4)

GRANT CONNECT ON ENDPOINT::[BROKER] TO [SIMONEX];

What's wrong in my code?

Many thanks.

Which server traces the handshake failed error? This error is related to the endpoint authentication, so the problem is from the certificates in [master] ([Certificato1] and [Certificato2]). Please make sure that the right certificates are deployed: look into master.sys.certificates and validate that the certificate thumbprints match between the two instances.

If I'd have to make a wild guess, I'd say: try adding a start/expiration date to the certificates you create. There is a problem with certs created in the eastern UTC time zones that makes the certs unusable for a numbers of hours if a start date is not provided. I believe Italy is GMT-1, so the problem would manifest as the handshake failing for 1 hour after the certs are created, then it would start working.

HTH,
~ Remus

Certificate not found

Hello I haw trouble getting the service broker to work I have 3 instances of SQL servers:

1 “Sender”SQL 2005 Server

2 “Receiver 1” SQLEXPRESS 2005

3 “Recevier 2” SQLEXPRESS 2005

What I wont is to be abele to do is to send a message from “Sender” to “Receiver 1” or “Recevier 2”.

I am abele to send a message from “Sender” to “Receiver 1” but if I send a message to “Receiver 2” I get a dialog security problem I think. If I use profiler I can se in “Receiver 2” the events:

Broker:Connection

Audit Broker Login

Broker:Message Classify

Audit Broker Conversation = Certificate not found

Broker:Message Undeliverable

And I cant find what′s wrong, this Is my scripts for etch instance.

“Sender”

USE master

CREATE CERTIFICATE Cert_ROBOTSRV

WITH SUBJECT = 'Cert_ROBOTSRV_auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_ROBOTSRV TO FILE = 'C:\Cert_ROBOTSRV'

GO

CREATE ENDPOINT SBEndpointServer STATE = STARTED

AS TCP (LISTENER_PORT = 5723)

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE Cert_ROBOTSRV)

GO

CREATE USER andon

CREATE CERTIFICATE Cert_sevapc311_pub AUTHORIZATION andon

FROM FILE = 'C:\Cert_sevapc311'

--DROP CERTIFICATE Cert_Andonpc017_Trans

CREATE CERTIFICATE Cert_Andonpc017_Trans AUTHORIZATION andon

FROM FILE = 'C:\Cert_andonpc017_Trans'

--DROP LOGIN sbLogin

CREATE LOGIN sbLogin

FROM CERTIFICATE Cert_Andonpc017_Trans;

GO

GRANT CONNECT ON ENDPOINT::SBEndpointServer TO [public]

GRANT CONNECT ON ENDPOINT::SBEndpointServer TO andon

GO

-

USE AndonDB

CREATE ROUTE Grafik_sevapc311

WITH SERVICE_NAME = 'Grafik_Service_Recive_sevapc311',

BROKER_INSTANCE = '7C737F42-2DF6-46E7-A6B6-89D1A9608DE2',

ADDRESS = 'TCP://sevapc311:5723'

GO

--DROP ROUTE Grafik_andonpc017

CREATE ROUTE Grafik_Andonpc017

WITH SERVICE_NAME = 'Grafik_Service_Recive_Andonpc017',

BROKER_INSTANCE = 'AE2B294A-B02E-4709-A51E-CFBFD0E478C1',

ADDRESS = 'TCP://192.168.20.106:5723'

GO

CREATE CERTIFICATE Cert_ROBOTSRV_Dialog

WITH SUBJECT = 'Cert_ROBOTSRV_auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_ROBOTSRV_Dialog TO FILE = 'C:\Cert_ROBOTSRV_Dialog'

GO

CREATE CERTIFICATE Cert_sevapc311_pub_Dialog AUTHORIZATION andon

FROM FILE = 'C:\Cert_sevapc311_Dialog'

--DROP CERTIFICATE Cert_andonpc017_Dialog

CREATE CERTIFICATE Cert_Andonpc017_Dialog AUTHORIZATION andon

FROM FILE = 'C:\Cert_andonpc017_Dialog'

GRANT SEND ON SERVICE::[Grafik_Service_Send_ROBOTSRV]

TO andon

CREATE USER sbLogin

GRANT SEND ON SERVICE::[Grafik_Service_Send_ROBOTSRV]

TO sbLogin

GO

--GRANT SEND ON SERVICE::[Grafik_Service_Send_ROBOTSRV]

--TO [VADERSTAD\vrobot]

--GO

-- Grant RECEIVE permission on the queue.

GRANT RECEIVE ON [Grafik_Queue]

TO andon

GO

GRANT CONTROL ON SERVICE::[Grafik_Service_Send_ROBOTSRV]

TO andon

GO

--DROP REMOTE SERVICE BINDING Grafik_sevap

CREATE REMOTE SERVICE BINDING Grafik_sevap

TO SERVICE 'Grafik_Service_Recive_sevapc311'

WITH USER = andon

GO

--DROP REMOTE SERVICE BINDING Grafik_andonpc017

CREATE REMOTE SERVICE BINDING Grafik_andonpc017

TO SERVICE 'Grafik_Service_Recive_Andonpc017'

WITH USER = andon

“Receiver 1”

use master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'

CREATE CERTIFICATE Cert_sevapc311

WITH SUBJECT = 'Cert_sevapc311_Auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_sevapc311 To FILE = 'C:\Cert_sevapc311'

CREATE ENDPOINT SBEndpointklient STATE = STARTED

AS TCP (LISTENER_PORT = 5723)

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE Cert_sevapc311)

CREATE CERTIFICATE Cert_ROBOTSRV_pub AUTHORIZATION andon

FROM FILE = 'C:\Cert_ROBOTSRV';

GRANT CONNECT ON ENDPOINT::SBEndpointklient to andon

-

use KlientDB

GRANT SEND ON SERVICE::[Grafik_Service_Recive_sevapc311]

TO andon

GO

GRANT CONTROL ON SERVICE::[Grafik_Service_Recive_sevapc311]

TO andon

GO

GRANT RECEIVE ON [Grafik_Queue]

TO andon

GO

CREATE Route Grafik_ROBOTSRV

WITH

SERVICE_NAME = 'Grafik_Service_Send_ROBOTSRV',

BROKER_INSTANCE = '2BA192F8-0BA3-4237-A156-21AFF7C65481',

ADDRESS = 'TCP://ROBOTSRV:5723'

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'

CREATE CERTIFICATE Cert_sevapc311_Dialog

WITH SUBJECT = 'Cert_sevapc311_Auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_sevapc311_Dialog To FILE = 'C:\Cert_sevapc311_Dialog'

CREATE CERTIFICATE Cert_ROBOTSRV_pub_Dialog AUTHORIZATION andon

FROM FILE = 'C:\Cert_ROBOTSRV_Dialog';

“Receiver 2”

use master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'

--DROP CERTIFICATE Cert_Andonpc017_Trans

CREATE CERTIFICATE Cert_Andonpc017_Trans

WITH SUBJECT = 'Cert_Andonpc017_Auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_Andonpc017_Trans To FILE = 'F:\Cert_Andonpc017_Trans'

CREATE ENDPOINT SBEndpointklient STATE = STARTED

AS TCP (LISTENER_PORT = 5723)

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE Cert_Andonpc017_Trans)

--DROP USER andon

CREATE USER andon

--DROP CERTIFICATE Cert_ROBOTSRV

CREATE CERTIFICATE Cert_ROBOTSRV AUTHORIZATION andon

FROM FILE = 'C:\Cert_ROBOTSRV';

CREATE LOGIN sbLogin

FROM CERTIFICATE Cert_ROBOTSRV;

GO

GRANT CONNECT ON ENDPOINT::SBEndpointklient TO [public]

--Select * from sys.certificates

use KlientDB

create user andon

Grant SEND ON SERVICE::[Grafik_Service_Recive_Andonpc017] to [Public]

GRANT SEND ON SERVICE::[Grafik_Service_Recive_Andonpc017]

TO andon

GO

GRANT CONTROL ON SERVICE::[Grafik_Service_Recive_Andonpc017]

TO andon

GO

GRANT RECEIVE ON [Grafik_Queue]

TO andon

GO

--DROP Route Grafik_ROBOTSRV

CREATE Route Grafik_ROBOTSRV

WITH

SERVICE_NAME = 'Grafik_Service_Send_ROBOTSRV',

BROKER_INSTANCE = '2BA192F8-0BA3-4237-A156-21AFF7C65481',

ADDRESS = 'TCP://ROBOTSRV:5723'

--Dialog S?kerhet

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'

--Drop CERTIFICATE Cert_Andonpc017_Dialog

CREATE CERTIFICATE Cert_Andonpc017_Dialog

WITH SUBJECT = 'Cert_Andonpc017_Auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_Andonpc017_Dialog To FILE = 'F:\Cert_Andonpc017_Dialog'

--Drop CERTIFICATE Cert_ROBOTSRV_Dialog

CREATE CERTIFICATE Cert_ROBOTSRV_Dialog AUTHORIZATION andon

FROM FILE = 'C:\Cert_ROBOTSRV_Dialog';

The Security Audit:Audit Broker Conversation event details will contain the Issuer Name and Serial Number of the certificate not found (I think they are the UserName and RemoteLogin columns). You can then look up sys.certificates on the sender side to make sure the sender is using the certificates you expect.

HTH,
~ Remus

|||

Ok thanks.

I will look if I can find what′s wrong.

I got it to work when I turned of the dialog security by deleting RSB.