Re: DBO Best Practices

From: Bill Hollinshead [MS] (billhol@online.microsoft.com)
Date: 10/23/02 

From: billhol@online.microsoft.com ("Bill Hollinshead [MS]")
Date: Wed, 23 Oct 2002 15:26:53 GMT

Hi Tim,

Somewhat of an aside: If you let DBO own databases or objects then regular
users (those who are not sysadmins, db_owners, etc) will not be able to
alter those same objects - which is why
http://msdn.microsoft.com/library/en-us/vdtsql/dvmsgstx_userisnotdbo.asp
can be seen. On the other hand, using DBO as the object owner does
unfortunately allow developers to write 'lazy' or 'sloppy' <g> code that
doesn't explicitly reference the object's owner (because SQL Server will
look for an object owned by the current user and if not found will
automatically look the object owned by DBO). This lazy practice is sloppy
because such T-SQL's execution plan, even though cached, cannot be reused
by other users. This doesn't mean DBO shouldn't own the objects, it just
means that is best to always reference an object's owner.

Please note that the DBO user is identified by the SQL Server process
(i.e., the SQL Server application) - see
http://msdn.microsoft.com/library/en-us/adminsql/ad_security_9qyh.asp. Thus
the permissions of a DBO (within SQL Server's process space) relate to the
objects that SQL Server provides its logins (databases, tables, views,
etc.). Even so, the role membership of a user also affects how SQL Server
allows a external process to be spawned - for example, SQL Server considers
the role membership of a SQL Server user if that user attempts to run
xp_cmdshell - see the "Important" note under the Permissions section of
http://msdn.microsoft.com/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp.

In contrast to user permissions, the SQL Server startup account (for the
SQL Server process) controls SQL Server's ability to work with NT's objects
(files, hives, log on as a service, etc.). The startup account defines the
security context of the SQL Server process, and this definition is well
before the SQL Server process accepts logins (and thus well before SQL
Server identifies any particular SQL Server login as a user or a member of
a role, and thus before a DBO has been identified). Because a DBO user can
spawn a process under the security context of the SQL Server startup
account, it usually is best to allow the SQL Server process have the
minimum permissions needed for SQL Server to function. See
http://msdn.microsoft.com/library/en-us/instsql/in_overview_6k1f.asp.

And because a DBO user can gain the security context of the SQL Server
startup account, it is important to 1) avoid letting clients login as a
member of the sysadmin role and thus become a DBO user (unless specifically
desired), and 2) avoid giving the SQL Server startup account more
permissions than needed. Otherwise, those who are DBO (and hopefully there
will be very few that are allowed to be identified as such) and run
xp_cmdshell will gain the SQL Server startup account's security context
(i.e., permissions).

Far more information about security is available at
http://www.microsoft.com/sql/techinfo/administration/2000/security.asp,
especially within the Security White Paper.

Note that it does not matter whether the same NT account both starts the
SQL Server service and is identified (once SQL Server starts) to be a
member of SQL Server's sysadmin role (i.e., identified as a DBO). These two
accounts are very separate, one is a NT account and the other is a SQL
Server login. SQL Server will not be aware that they are the same account
even when SQL Server and NT are configured in such a manner <g>.

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Relevant Pages

  • Re: DBO Best Practices
    ... lines of always owner qualifying objects... ... someone who are just "a" dbo. ... > doesn't explicitly reference the object's owner (because SQL Server will ... The startup account defines the ...
    (microsoft.public.sqlserver.security)
  • Re: ASP.net insists on using dbo. to call aspnet_* stored procedures
    ... SQL Server 2000 developer in same machine. ... connection string is setup to use the sql login assigned to the ... connectivity problem than a dbo problem. ... was not allowing my sql account access to these. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: ASP.net insists on using dbo. to call aspnet_* stored procedures
    ... No. mysql_login is one account and the dbo is another account, ... SQL Server 2000 developer in same machine. ... connection string is setup to use the sql login assigned to the ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: ASP.net insists on using dbo. to call aspnet_* stored procedures
    ... dbo is the schema used in SQL Server and, even if removed from queries, is technically still there. ... It sounds more like a permissions error or connectivity problem than a dbo problem. ... Is SQL Server on the same box as your application? ... the remote server once again, but I still get errors like this: ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: ADP/SQL Server 2000 Security Problem
    ... > I originally added the database by detaching it from MSDE 2000 and then ... > I'll also test using a SQL Server account and see what happens. ... >> The owner of the dbo account shouldn't have any effect. ... there are no SQL Server user accounts. ...
    (microsoft.public.access.adp.sqlserver)