Database ownership best practice

Does it matter much who owns a database? I'm reviewing security on our
SQL installation and find a number of different owners: sa, Domain
administrators and some others. I want to tidy this up. Is it best to
make an NT administrator the owner of all databases, assign a special
user which does nothing else but own databases?
Having looked at Books Online and elsewhere, I don't see any advice as
broad based as what I'm seeking.
Any suggestions would be welcome.I think it's mostly an audit trail, to see who created the database.|||Hi
Our corporate standard is that all objects must be owned by SA. If it owned
by a domain user, or a SQL Server login, if that user leaves you have to
change the ownership of the object before you can remove the user from the
DB/system.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<stevester@.freeuk.com> wrote in message
news:1123238913.304851.90500@.g44g2000cwa.googlegroups.com...
> Does it matter much who owns a database? I'm reviewing security on our
> SQL installation and find a number of different owners: sa, Domain
> administrators and some others. I want to tidy this up. Is it best to
> make an NT administrator the owner of all databases, assign a special
> user which does nothing else but own databases?
> Having looked at Books Online and elsewhere, I don't see any advice as
> broad based as what I'm seeking.
> Any suggestions would be welcome.
>|||Like Mike Epprecht said, it's a good practice to use a standard SQL login as
the database owner since this will ensure the database owner account is
never orphaned. An orphaned database owner account can have side affects,
such as problems using sp_helpdb or using Enterprise Manager. If only
sysadmin role members can create database objects on your SQL Server, 'sa'
database ownership is probably ok. However, I'd like to add some additional
notes on the importance of the database owner.
The login associated with the database owner determines the mapping of the
database's 'dbo' user. 'dbo' has full permissions within the database so
the database owner login account needs to be appropriately secured.
Furthermore, when cross-database chaining is enabled (configurable option in
SQL 2000 SP3+ and always on in older SPs/versions), dbo-owned views, procs,
functions and triggers can access dbo-owned objects in other databases owned
by the same login even without user permissions on the referenced objects.
This is a major security concern with sa-owned databases because master is
also owned by 'sa' and is why one should enable cross-database chaining in
an sa-owned database when only sysadmin role members can create dbo-owned
objects. It is best to avoid turning on cross-database chaining unless
absolutely necessary.
If non-sysadmin role members can create dbo-owned database objects and you
must enable cross-database chaining, you should instead consider creating
and using a specialized SQL login for database ownership. See the Books
Online for more information on cross-database chaining.
Hope this helps.
Dan Guzman
SQL Server MVP
<stevester@.freeuk.com> wrote in message
news:1123238913.304851.90500@.g44g2000cwa.googlegroups.com...
> Does it matter much who owns a database? I'm reviewing security on our
> SQL installation and find a number of different owners: sa, Domain
> administrators and some others. I want to tidy this up. Is it best to
> make an NT administrator the owner of all databases, assign a special
> user which does nothing else but own databases?
> Having looked at Books Online and elsewhere, I don't see any advice as
> broad based as what I'm seeking.
> Any suggestions would be welcome.
>|||The owner info is of limited use from an audit perspective because the
database owner can be subsequently changed. See my response on security
considerations.
Hope this helps.
Dan Guzman
SQL Server MVP
<MICHAEL_SUNLIN@.COUNTRYWIDE.COM> wrote in message
news:1123249058.756243.222770@.z14g2000cwz.googlegroups.com...
>I think it's mostly an audit trail, to see who created the database.
>|||Thanks for the advice. I liked the idea of a dedicated db owner, to
keep things modular, but the orphaned user concern is a good point.
I'll review those areas mentioned and go with sa as long as the
associated points are not a problem.

database owners

As I am making\changing databases I have, I started thinking about who should be the owners of what database.

Should they all be owned by sa or should I break them out? Like the WebAppDB1 is owned by BJohnson10, who is works mainly with this db as he is lead developer of the Web App that uses the database.

If you need any additional info, reply.

Thank You in advance!!!Hi Unotech

This is a great question, unfortunately there isnt a simple answer.

If you create objects when logged in as sa, then they by default become the porperty of dbo. This seems to be what happens in most databases, and isnt a problem.

If you have other users logging on, and creating objects from their own accounts, then any objects they create will be owned by them. In your example this is BJohnson10. This isnt a problem either.

However now you have the situation where some objects are owned by dbo, and some are owned by BJohnson10, so when a developer comes to write some T-SQL, they have to know who owns the object they want to refer to, and explicitly refer to them using the fully qualified name like this

<Owner>.<Object Name> or BJohnson10.Table1

For objects owned by dbo, this is easier as any object reference automatically defaults to dbo as the owner, so can be referenced just as <Object Name> or Table2. Although the fully qualified name is dbo.Table2, the system manages the dbo part by default.

Another dimension to this whole question is that of security. For more information on creating secure databases solutions, take a look at the latest materials on this at

http://msdn.microsoft.com/practices/compcat/default.aspx?pull=/library/en-us/dnnetsec/html/threatcounter.asp
and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/secnetlpMSDN.asp?frame=true

Regards

Steve

database owners

As I am making\changing databases I have, I started thinking about who should be the owners of what database.

Should they all be owned by sa or should I break them out? Like the WebAppDB1 is owned by BJohnson10, who is works mainly with this db as he is lead developer of the Web App that uses the database.

If you need any additional info, reply.

Thank You in advance!!!Hi Unotech

This is a great question, unfortunately there isnt a simple answer.

If you create objects when logged in as sa, then they by default become the porperty of dbo. This seems to be what happens in most databases, and isnt a problem.

If you have other users logging on, and creating objects from their own accounts, then any objects they create will be owned by them. In your example this is BJohnson10. This isnt a problem either.

However now you have the situation where some objects are owned by dbo, and some are owned by BJohnson10, so when a developer comes to write some T-SQL, they have to know who owns the object they want to refer to, and explicitly refer to them using the fully qualified name like this

<Owner>.<Object Name> or BJohnson10.Table1

For objects owned by dbo, this is easier as any object reference automatically defaults to dbo as the owner, so can be referenced just as <Object Name> or Table2. Although the fully qualified name is dbo.Table2, the system manages the dbo part by default.

Another dimension to this whole question is that of security. For more information on creating secure databases solutions, take a look at the latest materials on this at

http://msdn.microsoft.com/practices/compcat/default.aspx?pull=/library/en-us/dnnetsec/html/threatcounter.asp
and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/secnetlpMSDN.asp?frame=true

Regards

Steve

database owner with restrictions

I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroupdb_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj>). See this for other predefined roles:
http://msdn.microsoft.com/library/en-us/adminsql/ad_security_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup

database owner with restrictions

I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroup
db_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj>). See this for other predefined roles:
http://msdn.microsoft.com/library/en...urity_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup

database owner with restrictions

I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroupdb_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj> ). See this for other predefined roles:
http://msdn.microsoft.com/library/e...curity_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup