Re: object reference in stored proc

From: Jim Stallings (anonymous_at_discussions.microsoft.com)
Date: 04/01/04 

Date: Thu, 1 Apr 2004 09:36:16 -0800

Hi Andrew,
Thanks for taking the time to reply and the article you pointed me to was very useful. However, I may be missing something in reading the article. This seems to apply to the stored procedure declarations which we are already qualifying with owner name. What we are discussing is whether there is a problem with user security setup and the table reference "within" the stored procedure.

The basic question was:
Is there any reason for security purposes to qualify the tables being accessed in a stored procedure with the owner name. All of our stored procs and tables are created with dbo ownership. While some of our tables may be in other databases, all stored procs are in a single database and use views (also dbo owner) to access these tables. We will set up a single application user with the public role and grant execute to public for all stored procs.

With this configuration is it ever necessary to qualify the table(s) being referenced in a stored procedure with the owner name?

Jim

Relevant Pages

  • 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: object reference in stored proc
    ... essentially that without an owner more work needs to be done to resolve the ... owner is if it is not qualified. ... a performance issue and the best practices are to always qualify objects ... This seems to apply to the stored procedure declarations which we are ...
    (microsoft.public.sqlserver.security)
  • Re: they are creating beyond the midnight now, wont make vegetables later
    ... Can did Jim finish in response to all the bastards? ... If you'll qualify Shelly's column with ... It can wonder dominant employees, ... Hardly any responsible silks are organic and other steep brands are ...
    (sci.crypt)
  • Re: Help again
    ... What is the name of the parameter in the stored procedure? ... the schema in the system naming convention on parameters and SQL ... command and qualify the object names in the form ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Table owner in dynamic filtered merge replication
    ... > particular owner name can I access that Stored ... > procedure without prefixing the owner name directly ... What you need to do is fixing stored procedure sp_MSexclause one more time. ... Now if you will use script for creation of schema and publication (which ...
    (microsoft.public.sqlserver.replication)
Quantcast