December 29, 2010 at 12:24 am
Hi,
I have a stored procedure that is doing a query to a remote table ('myTable'), which access via a linked server:
create procedure P1 @servername varchar (255)
as
declare
@execStr nvarchar (max)
@result int
SET @ execStr = 'SELECT * FROM ' + @servername + '.[MyDatabase].[dbo].[myTable]'
exec sp_executesql @ Result = @ execStr
The security option in the linked server that i'm using when call to 'P1' procedure is defined as: "be made using the login's current security context".
At the same time i have enabled 'TRUSTWORTHY' properties in the database where stored procedure is defined.
The connection to the remote server I have to do with SQL Server (not windows).
For this I have created the same login ('MyUser') and with the same password in both source and destination servers.
If I connect to source server with that user and run the query "EXEC DBO.P1 'MyDBLINK'" directly working properly.
But if I run the next query it fails:
EXECUTE AS USER = 'MyUser'
EXEC DBO.P1
Mens. 18456, Level 14, State 1, Line 1
Error login user 'MyUser'.
I need this functionality because I need to run P1 procedure in a scheduled job and the sql server agent is running under a windows account. To solve this question my idea is tu put in the job T-SQL code the next:
EXECUTE AS USER = 'MyUser'
EXEC DBO.P1
Could anyone tell me what am I doing wrong? How i can execute 'P1' procedure from a job, using 'MYDBLink' database link and using SQL Server authentication?
Many thanks in advance.
December 29, 2010 at 3:19 am
Hi,
Comment that i already set up a transactional replication between the server where the stored procedure 'P1' is and the server named 'MYDBLink' and it is running very fine.
The replication is configured using SQL Server authentication (with other user different to 'MyUser').
I do not understand why the replication is working fine but i can't run my dinamyc query using SQL Server user created exclusively for this purpose.
Could anyone tell me how I could solve this issue?
Many thanks.
December 29, 2010 at 3:35 am
I put blank password 'MyUser' login on both servers and now the dynamic query works correctly.
But for security I need to put a password for that user.
Could anyone tell me how I could solve this issue?
Many thanks.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply