database ownership question

Our SQL Server DBA has left the company with many of the databases owned by his domain user. I understand that I could change database ownership using sp_changedbowner. Does anyone know what would be a better option, from security point of view?

1. Let databases owned by 'sa' like all system databases.
2. Let databases owner by the domain account that runs SQL Service.
3. Create a local/domain account which owns all databases on this SQL Server instance.
4. Let databases owned by Administrator account.

I am new to SQL Server. Help would be appreciated.Howdy

'sa' is the best way to go.

By all means, set up SQL & SQL Agent to run using a domain account unless it needs to be a highly secure box.

By the way, any account who is in the local Administrators group ( this is NT security I'm talking about ) on the box, is automatically sa ( potential LARGE security hole ).

Post back if any more questions. Happy to answer.

Cheers

SG|||Thanks for the info, will try that.

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