Re: ADP: Can't use stored procedure on remote SQL server

With ADP, I found by experience that's not a good idea to have anything else
then dbo as the owner of everything. Not only I use dbo everywhere but I
also take the precaution of setting the Record Source Qualifier property
(under the Data tab) of all forms to dbo.

If you don't mention the owner when creating a new stored procedure, view,
table or function; it get associated with the current user account. This is
probably what happening to you.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Marcus" <marcus@xxxxxxxxxxxxxxxxxx> wrote in message
news:Pd-dnQwGzddbQ-TVnZ2dnUVZ8sXinZ2d@xxxxxxxxxxxxxxxx
Yes, I'm using Windows Integrated security.... accessing the DB over the
Internet, surely doesn't gives any credentials....
Strange is that other queries and access to tables is enabled...
BTW in the database window, all the stored procedures are followed by
(xxxxUser) that id a SQL server username I have created... is the owner?
I'll post tomorrow what happens...

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uSmztEE5IHA.1748@xxxxxxxxxxxxxxxxxxxxxxx
No other port need to be open. I suppose that you might have some
permission issue here or that ADP have a problem opening some background
connection if you hare using Windows authentification over the VPN or the
WAN.

If you are using a Windows account, the first thing to try would be using
a SQL-Server login account.

Check also the owner of the SPInc stored procedure. Adding dbo. before
won't work at all if the owner is another account than dbo. What do you
see when you take a look at the Views/SP/Functions tab on the database
window?

You should also take a look with the SQL-Server Profiler in order to see
more exactly what could happen here.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Marcus" <marcus@xxxxxxxxxxxxxxxxxx> wrote in message
news:8M2dndOHhtExQ-XVnZ2dneKdnZydnZ2d@xxxxxxxxxxxxxxxx
Yes, as I've said it works perfectly when in LAN.... I'm wondering if
some other ports need to be accessed from the Internet or if some
credentials are passed automatically while in the Domain....
The strange is that everything works (SQL Queries, etc)


"a a r o n . k e m p f @ g m a i l . c o m" <aaron.kempf@xxxxxxxxx>
wrote in message
news:7ef0e47d-511a-4806-a44a-1b35d81e1740@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
do you have permissions to the sproc?



On Jul 10, 1:56 pm, "Marcus" <mar...@xxxxxxxxxxxxxxxxxx> wrote:
I'm happily using a remote SQL server over the internet using Sylvain
Lafontaine's suggestion:

"use a comma to specify the port number as in a.b.c.d,1433"

Unfortunatelly I have noticed that stored procedures have some
problems,
that is
they don't execute at all when launched remotely... is there a
particular
reason, in your opinion, why this happens? While in LAN, they work
perfectly....

The error is:

"Run-time error '2812' Could not find stored procedure 'SPInc'.

here's the offending code:
***********************

spstring = "exec SPInc '" & _
fGDataFine & _
"' , '" & _
fGDataInizio & _
"'," & _
Me.Anno & "," & _
Me.Mese & "," & _
AnnoPrec & "," & _
MesePrec & "," & _
spOrderBy

Me.RecordSource = spstring
**************************

I






.

Relevant Pages

  • Finding/Accessing object when changing of Owner (dbo)
    ... A user "PROC_USER" that is the owner of the stored procedure and function ... The end-users that will access the system will have a specific login like ... user "PROC_USER" in order to allow the stored procedure to access the data. ... the existance of the table in the current schema then it search in the DBO ...
    (microsoft.public.sqlserver.security)
  • Re: Stored Procedure Disappearing
    ... The owner of the sp and all of its component functions is dbo. ... summary report which obtains data from a large stored procedure containing ...
    (microsoft.public.access.reports)
  • Re:Stored Procedure Question
    ... When you execute a SP without any owner name by default ... SQL assumes it to be dbo. ... the stored procedure fire following query. ...
    (microsoft.public.sqlserver.security)
  • Re: object reference in stoed proc
    ... If you don't qualify the object sql server will first look for the object ... as such it will eventually try dbo. ... implications by not qualifying the objects with the owner. ... all object refences dbo by default anyway within a stored procedure? ...
    (microsoft.public.sqlserver.security)
  • Re: User rights problem
    ... You cant give permission to developer to create a stored procedure under the ... db_owner rights and execute the below command to change the owner to dbo. ... This will make the object owner to dbo. ...
    (microsoft.public.sqlserver.security)