Re:Stored Procedure Question

From: Vishal Parkar (vgparkar@hotmail.com)
Date: 03/20/03

  • Next message: Bala Neerumalla[MSFT]: "Re: Windows authentication failed on SQL2000 +NT4" 
    From: "Vishal Parkar" <vgparkar@hotmail.com>
Date: Wed, 19 Mar 2003 20:51:28 -0800

    When you execute a SP without any owner name by default
    SQL assumes it to be dbo. If SP is owned by any other user
    than dbo then you may get message as "Could not find
    stored procedure....". You will have to execute SP by
    prefixing the username to the SP name. To confirm owner of
    the stored procedure fire following query.

    select a.name, user_name(a.uid) 'owner'
    from dbo.sysobjects a
    where OBJECTPROPERTY(a.id, N'IsProcedure') = 1
    order by a.name

    --Vishal

    >-----Original Message-----
    >If I call a development stored procedure owned by me
    >(ulu96a) from Production Modeler, I have to prefix the
    >name of the stored procedure with ulu96a. Otherwise I
    >cannot access the stored procedure. When we migrate our
    >Production Modeler files to the string test environment
    >where the stored procedures are owned by dbo we need to
    >remove the user id prefix from every call, of which there
    >are many. I don't know if our problem in the development
    >environment is a result of privileges on the LRP_PM_DCUST
    >account we use to connect to the database or with
    >privileges on the stored procedures themselves. Do you
    >have any suggestions on how we can fix this? Is it an
    >account privilege problem?
    >.
    >

  • Next message: Bala Neerumalla[MSFT]: "Re: Windows authentication failed on SQL2000 +NT4"

    Relevant Pages

    • Re: ADP: Cant use stored procedure on remote SQL server
      ... Not only I use dbo everywhere but I ... If you don't mention the owner when creating a new stored procedure, view, ... BTW in the database window, all the stored procedures are followed by ... Check also the owner of the SPInc stored procedure. ...
      (microsoft.public.access.adp.sqlserver)
    • 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)
    • Change owner to sp_prueba
      ... I need to change the owner of a stored procedure called sp_prueba ... created by a user time ago. ... When I try to execute sp_changeobjectowner 'sp_prueba', dbo an error ...
      (microsoft.public.sqlserver)
    • 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)