Access to the remote server is denied because no login-mapping exists

Question

Access to the remote server is denied because no login-mapping exists

RightOnTarget

Hall of Fame

Points: 3041
More actions
January 28, 2014 at 11:41 am

#288823

Hi all,
I created an app user and granted EXEC privs to run a procedure.
There are no linked server calls in the procedure, but there are 3 instances of the code below.
I am assuming that it is a cause of the error.
How do i go about fixing it?
SET @sql =
N' INSERT INTO #temp_list
SELECT *
FROM OPENROWSET ( ''SQLOLEDB''
,''Server=(local);TRUSTED_CONNECTION=YES;''
,''set fmtonly off EXEC local_db.schema.proc '''''+@var1+''''','''''+@var2+''''''')';
EXEC (@SQL);

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 1

rightontarget (1/28/2014)
Hi all,
I created an app user and granted EXEC privs to run a procedure.
There are no linked server calls in the procedure, but there are 3 instances of the code below.
I am assuming that it is a cause of the error.
How do i go about fixing it?
SET @sql =
N' INSERT INTO #temp_list
SELECT *
FROM OPENROWSET ( ''SQLOLEDB''
,''Server=(local);TRUSTED_CONNECTION=YES;''
,''set fmtonly off EXEC local_db.schema.proc '''''+@var1+''''','''''+@var2+''''''')';
EXEC (@SQL);

What is the whole error message? I won't even get into why someone is using an openrowset to run a SP on a database on the same server.

Why the heck not just:

INSERT INTO #temp_list

EXEC local_db.schema.proc @var1, @var2

Even if this were on a linked server and not local:

INSERT INTO #temp_list

EXEC servername.local_db.schema.proc @var1, @var2

This code makes no sense at all and, as it appears now (local server), buys you nothing but slowness and risk.

Can you elaborate on the message or the purpose for this code?

Thanks

John.

RightOnTarget Hall of Fame Points: 3041 More actions · Answer 2

John,

The reason for openrowset is because the called procedure calls another procedure where insert ... exec is used.

So, I am getting an error "INSERT EXEC statement cannot be nested"

I know, it may not make sense to YOU, but at the time it was the way for us to overcome the error and keep going.

Thanks for reply,

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 3

rightontarget (2/10/2014)
John,
The reason for openrowset is because the called procedure calls another procedure where insert ... exec is used.
So, I am getting an error "INSERT EXEC statement cannot be nested"
I know, it may not make sense to YOU, but at the time it was the way for us to overcome the error and keep going.
Thanks for reply,

I understand what you are saying, but I am merely trying to help.

There is a reason why SQL Server restricts this kind of thing.

Can you rephrase the top level sproc or the nested one into a table valued function?

See: http://www.sommarskog.se/share_data.html where this error is discussed along with good practices to get past it.

OPENQUERY is discussed and discouraged in the above URL, OPENROWSET is even worse.

Not trying to offend. Just trying to suggest a best practice.

Thanks

John.

RightOnTarget Hall of Fame Points: 3041 More actions · Answer 4

Thank you, I ended up finding and reading the same document.