Re: Is There any Resource for SQL Managment

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 07/10/04 

Date: Sat, 10 Jul 2004 15:28:04 -0400

Who owns the objects has little to do with who can access, update, modify
the objects etc. The owner by default has permission to do all of this but
you usually don't give the end user those rights to begin with. That is for
the developers or administrators. The end user should only have permissions
to do what they need and nothing more. This is typically done thru roles
that have the proper permissions granted, denied etc. In that case the
owner (dbo) or more correctly the schema should have nothing to do with
those granted permissions. Now if you are going to have 3 different
developers who need the ability to have full control over the objects (one
for each sub system who aren't already sa) and you don't want them to have
access to each others sub system that may be a different story. Then you can
have 3 different schema's and assign dbo rights appropriately. You can
also do the same by having 3 separate databases and all the objects can have
the default dbo schema. You deal with it at the user level them. Which is
the correct way depends a lot on how your data will be segmented, how your
security needs to be and who needs what level of access. Most people go
overboard with having different schemas without knowing why or having a good
reason so all I am asking is you plan ahead. Obviously you have a good
start since your asking the question in the first place. 

-- 
Andrew J. Kelly  SQL MVP
"Hoa" <newpoorguy@yahoo.com> wrote in message
news:%23RTQIQqZEHA.2016@TK2MSFTNGP09.phx.gbl...
> Thanks for your reply but I want to ask you about my second question:
>
> 2)Is it a good habit to use different owners for creating tables and views
>  to classified database objects or using "dbo " is recommended?
>
>  Imagine that we want to create a financial program with 3 sub systems :
> Accounting, Treasury and Payroll. Now we want to classified the database
so
> that the Accounting team work on their own tables and so on.
>
>  What's your suggestion? Using dbo.Acc(tablename) and dbo.Trs(tablename)
and
> dbo.Pay(tablename) or Acc.TableName (Acc as the owner) , Pay.TN ,...
>
>  Again, thx alot for your help and your time.
> HOA
>
>
>

Relevant Pages

  • Problem is w/ .ADP..Re: SQL db Permissions for users not working
    ... You do not have SELECT permissions on the ... SysObjects system table in the database. ... figured out that qualifying the database owner (dbo in my ... >> I feel that the object owner is not dbo, ...
    (microsoft.public.sqlserver.security)
  • Re: How to prevent DELETEs in a table
    ... It is the dbo database USER, not server-level groups, that determins ... It has implicit permissions that can not be denied. ... SQL Server just skips any permission validation for sysadmins. ...
    (microsoft.public.sqlserver.server)
  • Re: Control over creation of procs & views owned by dbo
    ... To add on to Jasper's response, you could also change object ownership to ... 'dbo' with sp_changeobjectowner. ... security context of the invoking user, not the object owner. ... need permissions on only directly referenced objects. ...
    (microsoft.public.sqlserver.security)
  • Re: not creating tables as dbo anymore ?
    ... Database Owner (dbo) ... existing user ID in the database specified by database_name. ... with the login of the current connection. ...
    (microsoft.public.sqlserver.security)
  • Re: Change UDF Owner
    ... Does it exist in the master database? ... Users can be put in the dbo role, but dbo will still own objects ... concept of 'schema' as 'owner' rather than just schema as DDL is exploited ... to the appropriate Fixed Server Roles or whatever is applicable. ...
    (microsoft.public.sqlserver.security)
Loading