Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Eli Leiba » Methods For Converting a Stored Procedure
Add to briefcase
38 posts, Page 3 of 4
«««1234»»

Methods For Converting a Stored Procedure Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
David McKinney
Posted Thursday, October 29, 2009 9:39 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:43 AM
Points: 582, Visits: 1,601
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?
Post #810909
Ramon-218872
Posted Thursday, October 29, 2009 10:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 5:06 AM
Points: 9, Visits: 49
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
Post #810949
Alexander Kuznetsov
Posted Thursday, October 29, 2009 10:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 21, 2013 8:55 AM
Points: 219, Visits: 807
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?
Post #810973
Tom Garth
Posted Thursday, October 29, 2009 10:32 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #810979
Ramon-218872
Posted Thursday, October 29, 2009 10:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 5:06 AM
Points: 9, Visits: 49
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.
Post #810989
Tom Garth
Posted Thursday, October 29, 2009 11:07 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #811015
Ramon-218872
Posted Thursday, October 29, 2009 11:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 5:06 AM
Points: 9, Visits: 49
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...
Post #811037
Tom Garth
Posted Thursday, October 29, 2009 11:30 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #811046
John Fager
Posted Thursday, October 29, 2009 11:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 23, 2012 11:12 AM
Points: 4, Visits: 27
The security implications of this and lack of discussion and warning on them really bothers me.
Post #811058
Toby White
Posted Thursday, October 29, 2009 3:11 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 09, 2012 2:48 PM
Points: 493, Visits: 636
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.
Post #811240
« Prev Topic | Next Topic »

Add to briefcase
38 posts, Page 3 of 4
«««1234»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.