Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Best Practice for Database Owner? Expand / Collapse
Author
Message
Posted Thursday, July 23, 2009 8:08 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
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.
Post #758243
Posted Thursday, July 23, 2009 8:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 1, 2014 1:26 PM
Points: 2,242, Visits: 3,645
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
Post #758285
Posted Thursday, July 23, 2009 9:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 9:02 PM
Points: 33,153, Visits: 15,284
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
Post #758340
Posted Thursday, July 23, 2009 9:31 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
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
Post #758351
Posted Friday, July 9, 2010 4:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 6:37 PM
Points: 2, 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
Post #949827
Posted Friday, July 9, 2010 5:11 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 7, 2014 1:54 PM
Points: 450, Visits: 1,337
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
Post #949846
Posted Sunday, July 11, 2010 11:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 6:37 PM
Points: 2, 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.
Post #950496
Posted Monday, July 19, 2010 4:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 2,867, Visits: 3,207
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 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #954691
Posted Friday, August 6, 2010 10:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 26, 2014 10:26 AM
Points: 141, Visits: 354
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.
Post #965300
Posted Monday, August 9, 2010 4:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 2,867, Visits: 3,207
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 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #965817
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse