Re: Chain of ownership

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 06/23/04 

Date: Wed, 23 Jun 2004 07:59:04 -0400

The first thing is ... individuals should NOT own objects... You must have a
really good reason to allow John to own any object...Because you are hard
coding his user name is all of your Sps, views, and client apps...

What will you do when John leaves the company?...

In any case, the front end app can use ANY login, which maps to a user in
the database which has permissions on the Stored procedure AND permissions
on Johns table..

IF you had an unbroken chain of ownership... ie DBO OWNS Everything... You
would ONLY have to have permission on the SP... So the permission structure
gets much simpler...

If you are in a position where you might re-think allowing broken chains of
ownership - please consider enforcing a 'DBO OWNS EVERYTHING' policy.

you will find your apps will be easier to maintain, less error prone, and
that security in SQL will also be easier, and performance will improve... 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alan" <NOSPAMalan_pltse@yahoo.com.au> wrote in message
news:u35yL2OWEHA.2844@TK2MSFTNGP11.phx.gbl...
> In our development environment, I am writing a stored procedure using
> another's table like 'John.Customer', so John need to grant me the select,
> insert...ect access, right ?
>
> Can someone explain when there is client application like VB, VC++ or
Delphi
> (I think it does not matter what languages) connect to the SQL Server, if
> that client application needs to call this stored procedure, what
> login/password it needs ?
>
>

Relevant Pages

  • Re: Deny access to all users (including Administrator and DomainAd
    ... permissions on stored procedure override ... applictaion's user account permissions to the stored procedure which will ... Will try to deny access through the query for SQL Server 2005 and 2000. ... Remove everyone that you don't want rom an Administrator Groups ...
    (microsoft.public.sqlserver.security)
  • Re: Set perissions on stored procedure
    ... stored procedure for one of my database roles, ... stored procedure and choosing All Tasks -> Manage Permissions. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Re: Stored procedure loses permissions daily
    ... Columnist, SQL Server Professional ... Senior Database Administrator ... > I have a stored procedure in a SQL 2000 database that loses all of the ... permissions back to what it should be -- very frustrating. ...
    (microsoft.public.sqlserver.security)
  • Re: SQL 2000 Sproc Role security bypassed when using QBF?
    ... procedures relies on the fact that the stored procedure and the tables ... to grant permissions through stored procedures than ownership chaining. ... This article also discusses ownership chaining in more detail. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Re: listbox and sql stored procedure
    ... Vyas, MVP (SQL Server) ... "John" wrote in message ... I've got listbox in my .aspx page where the users can make multiple ... items and pass it to stored procedure to delete 7 rolls in my table. ...
    (microsoft.public.sqlserver.server)