Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Methods For Converting a Stored Procedure


Methods For Converting a Stored Procedure

Author
Message
David McKinney
David McKinney
Right there with Babe
Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)

Group: General Forum Members
Points: 771 Visits: 2090
Ramon-218872 (10/29/2009)
Hi to all,

I'm trying to use the code, because it's that I need, but I get the error:

Msg 7357, Level 16, State 2, Procedure sp_ConvProc2View, Line 27
Cannot process the object "SET FMTONLY OFF EXEC sp_ControlStock". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.


Some body can help me?

Thanks in advance...


Do you have exec permssions on the stored procedure with the name sp_ControlStock?

i.e. if you type 'EXEC sp_ControlStock' (with no parameters) does that return you some rows?
Ramon-218872
Ramon-218872
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 112
Yes I can exec sp_ControlStock without problems...

I make some tests:

exec SGANPS.dbo.sp_ControlStock

It works

SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
'SELECT *
FROM SGANPS.dbo.ARTICULOS');

It works

SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
'exec SGANPS.dbo.sp_ControlStock');

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "exec SGANPS.dbo.sp_ControlStock". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

Some suggestion?

Thanks in advance
Alexander Kuznetsov
Alexander Kuznetsov
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 824
I would avoid such approaches as overly complex. I don't even want to think if this is secure or not. It is so very much easier to just reuse code wrapped in UDFs, so why would anyone want to go for a much more complex approach?
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 1499
Ramon-218872 (10/29/2009)
Yes I can exec sp_ControlStock without problems...

I make some tests:

exec SGANPS.dbo.sp_ControlStock

It works

SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
'SELECT *
FROM SGANPS.dbo.ARTICULOS');

It works

SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
'exec SGANPS.dbo.sp_ControlStock');

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "exec SGANPS.dbo.sp_ControlStock". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

Some suggestion?

Thanks in advance


I think I'm beginning to confuse myself. However while your credentials are passed along to the '.' linked version of your server, will the linked server acknowledge them for that procedure? Try -
SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
'exec .dbo.sp_who');



Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Ramon-218872
Ramon-218872
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 112
Hi,

SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
'exec .dbo.sp_who');

It works

But:

SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
'exec .dbo.sp_ControlStock');

Gets:

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "exec .dbo.sp_ControlStock". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 1499
Ramon,
I told you I was confusing myself.
USE TEST
GO

CREATE PROCEDURE dbo.sp_ControlStock AS SELECT 'ONE', 'TWO', 'THREE'
GO

SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
'exec TEST.dbo.sp_ControlStock');
GO


That works fine for me. Maybe you should start over, or go another route.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Ramon-218872
Ramon-218872
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 112
I'm not sure but perpahs the problem it's that in sp_ControlStock I'm using temporal tables or OPENQUERY statments because if I use a "simple" store procedure, now it works...
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 1499
That explains a lot. Like
linked server "(null)"

It sounds like your OPENROWSET statement within the procedure isn't passing suitable credentials.

Check BOL for Linked Server Security and Delegates regarding double hops.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

John Fager
John Fager
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 43
The security implications of this and lack of discussion and warning on them really bothers me.
Toby White
Toby White
Mr or Mrs. 500
Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)

Group: General Forum Members
Points: 503 Visits: 639
It's the double hop issue that always prevents me from using the

Trusted_Connection=yes

However, if the account that runs your SQL Server instance has the ability to register its own SPN as is explained at

http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx

then you should find that the connection you have open is using Kerberos authentication with the following query:

select auth_scheme from sys.dm_exec_connections
where session_id = @@Spid

If it is NTLM then the service account of SQL probably doesn't have the permission to register it's own SPN which causes security to collapse to NTLM which has the affect of disallowing the SQL instance to trust a security token passed to it. The double hop then becomes impossible and the security context is always lost on the second hop. If you are having trouble with the openrowset across servers with error:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

then check out http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx

This only applies to trusted because there is no security token passed with SQL authentication.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search