Re: object reference in stoed proc

From: Andrew J. Kelly (sqlmvpnoooospam_at_shadhawk.com)
Date: 04/01/04 

Date: Wed, 31 Mar 2004 20:36:27 -0600

If you don't qualify the object sql server will first look for the object
under the owner of the current user (not the role) and if it doesn't find it
as such it will eventually try dbo. There are definite performance
implications by not qualifying the objects with the owner. See here for
more details:

http://support.microsoft.com/default.aspx?scid=kb;en-us;263889&Product=sql2k 

--
Andrew J. Kelly  SQL MVP
"Jim Stallings" <anonymous@discussions.microsoft.com> wrote in message
news:BE7D2D54-F315-4D80-912F-02E4E7B0C26E@microsoft.com...
> Hello,
> We have multiple databases that are accessed by stored procedures in a
central database via views.  We have replication set up for these databases
and an issue came up about security and object owners.  It has been
suggested that we should fully qualify all table reference within stored
procedures to prevent issues with security and object ownership.
>
> My question is if whether qualifying the stored procedure declaration with
the owner name (dbo) defaults the internal object references to dbo?  Aren't
all object refences dbo by default anyway within a stored procedure?
>

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)
  • 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: Who owns the tables in development environment? (upsizing from Access)
    ... qualify the owner as dbo when doing Access upsizing. ...
    (microsoft.public.sqlserver.clients)