SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best Practice for Database Owner?


Best Practice for Database Owner?

Author
Message
Rich Mechaber
Rich Mechaber
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2727 Visits: 3671
I have a SQL 2005 server with a dozen databases in production use. I'd like to know what is recommended to assign as the database owner? We're running in mixed mode.

My thought is to assign ownership of our production databases to a Windows group of admins responsible for the system. They have been assigned a login to the SQL server.

Or would setting the owner to sa be preferable?

Thanks,
Rich M.
ps.
ps.
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7087 Visits: 3668
I dont think owning a database by sa or any other user should be of any concern. What matters is who is performing 'what' in your database. So creating users with only sufficient privilages should suffice.
For simplicity, you can have the owner as sa.
May be other will come up with different views on this.



Pradeep Singh
Steve Jones
Steve Jones
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: Administrators
Points: 145205 Visits: 19425
I don't remember this being a concern for me ever. Other than looking annoying in reports, or being unable to remove the user if they owner a database, but I don't think it affects server operation. you can just pick sa for consistency.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Rich Mechaber
Rich Mechaber
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2727 Visits: 3671
Thank you both very much. I didn't want individuals domain users to own databases, in case they leave the organization.

'Preciate the quick reply!
Rich
Ian Godfrey
Ian Godfrey
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 25
Hi, I hope the above advice still holds true.:-)

I have the same problem with my software application that creates a database and a bunch of SQL Agent jobs. By default these are created with the owner being the login associated with the account used to install the app. If the AD account is deleted later the login is 'orphaned'. Then the SQL agent jobs stop working, and there are problems creating new tables etc in the database.

A successful workaround is to make 'sa' owner. This works even if the 'sa' login is disabled, which is default if 'Windows Authentication only' is enabled.

The alternative to ‘sa’ would be to use a login associated with the application’s service account, but that may be problematic if that is deleted and replaced, and back to square one.

Which is better?

I have heard that some DBAs rename the ‘sa’ login. How do I find out what it is renamed to, so that the app installer can use that instead?

Will the following query be guaranteed to work?
select loginname from syslogins where sid=0x01

IanG
Leo.Miller
Leo.Miller
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2762 Visits: 1540
Ian Godfrey (7/9/2010)

If the AD account is deleted later the login is 'orphaned'. Then the SQL agent jobs stop working, and there are problems creating new tables etc in the database.

A successful workaround is to make 'sa' owner. This works even if the 'sa' login is disabled, which is default if 'Windows Authentication only' is enabled.

I have heard that some DBAs rename the ‘sa’ login.


If the domain account that ownes the database is deleted and the database is orphaned a number of things break. Normally none of them critical and none of them obvious untill you try look at the database properties from Managment Studio.
If you set SQL Server to run in Windows Only Mode, you don't "disable" the sa account, you only stop people from login on with the sa account and password. The account is "internal" to SQL and can't be deleted or disabled and as far as I know can't be renamed. Something I don't recommend you do.

The safest and recommended best practice is to have all dbs and jobs owned by sa. There are rare applications that require the database be owned by a spesific account, but you should know about those and I would normally ask the vendor to justify this or fix it, because there is no real reason for it. Normally just bad application implementation.

Stick to using sa and you'll save yourself some headaches later.

sp_changedbowner 'sa' Will do the trick.

Leo
Ian Godfrey
Ian Godfrey
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 25
Thanks for affirmation. :-)

I tried renaming sa. SQL Management Studio allows you to, but I haven't investigated what the impact is. The query I provided seems to work and provides the new name.
EdVassie
EdVassie
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13865 Visits: 3894
In SQL Server 2000 it used to be best practice to set the owner of all databases to the sa account.

With the introduction of database ownership chains this is no longer a safe thing to do. A user with database owner rights in a user database that is owned by sa will get elevated privileges in the master database that could allow them to hack your system.

My understanding of current best practice is to set the db owner of all user databases to a named account that has low privileges. The account can even be set to disabled in SQL Server, to prevent anyone using it.

If you have some user databases in an ownership chain, then these databases should have an owner account that is different to all databases not in that chain.

The FineBuild Reference document has a section on how to set up a database owner account with low privileges and change your user databases to use this account. The FineBuild scripts can do this for you automatically as part of a SQL Server install.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
ntran777
ntran777
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 384
EdVassie (7/19/2010)
With the introduction of database ownership chains this is no longer a safe thing to do. A user with database owner rights in a user database that is owned by sa will get elevated privileges in the master database that could allow them to hack your system.

My understanding of current best practice is to set the db owner of all user databases to a named account that has low privileges. The account can even be set to disabled in SQL Server, to prevent anyone using it.

If you have some user databases in an ownership chain, then these databases should have an owner account that is different to all databases not in that chain.


In order for the dbo in the user database to gain access to the master database wouldn't chain ownership have to be enabled?

I can understand why that would be a problem but, assuming it is DISABLED, what are the risks involved with having SA set to database owner on user databases?

From what I understand, they would only be able to access referenced views and tables on their own user database through chained ownership. This is redundant as they already have db_owner role and access to all objects on this database.
EdVassie
EdVassie
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13865 Visits: 3894
You are right in saying the ownership chaining would have to be enabled in your own DB for its owners to gain elevated rights in master. But it is often easier to manage a simple standard that says all user DBs should be owner by a low privilege account rather than a more complex standard that allows some to be owned by sa or another sysadmin account.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search