July 23, 2009 at 8:08 am
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.
July 23, 2009 at 8:36 am
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.
July 23, 2009 at 9:25 am
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.
July 23, 2009 at 9:31 am
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
July 9, 2010 at 4:04 am
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
July 9, 2010 at 5:11 am
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
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
July 11, 2010 at 11:34 am
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.
July 19, 2010 at 4:03 am
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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
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
August 6, 2010 at 10:14 am
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.
August 9, 2010 at 4:34 am
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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
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
August 9, 2010 at 2:00 pm
I see your point. Thanks for the reply!
October 30, 2012 at 7:20 am
This does matter for ownership chains (Impersonation. etc). For example if you attempt EXECUTE AS SELF, the dbowner will be used to execute. If the call is cross database, the dbowner may not have permissions in the target DB...broken wonership chain.
February 21, 2013 at 5:52 am
Hi!
Our practice is to create a service user/group in AD and assign that as the database owner. I have never been a fan of using the 'sa' account for anything other than emergency admin at the instance level and considering the level of authority the account has should IMHO not be used.
The other reason, and something that applies to us with the amount of databases we have, is that when I see a list of database users/groups, it would be nice to be able to associate them easier with the database. To that end we use AD Resource Groups and assign users to them. These users then have automatic db_owner on the database. Users of the database are then assigned to other reource groups with the appropriate permissions.
I think to say, as I have read in this post, that the owner is irrelevant is perhaps a little dangerous. The owner of the database automatically has (as expected) the right DB_OWNER which is the equivalent of God at the database level so some control over the owner needs to be had. Using 'sa' is also dangerous because, as expected, this is a SYSADMIN account with authority at the Instance-level! It doesn't take much to open a database query and run a query inside MASTER for example!
As far as possible, the rule of minimum privilege should be applied and Active Directory gives us the perfect tools to do that.
I hope that helped.....
February 25, 2013 at 8:40 am
Not a database owner issue but I did run into problems when a SQL job owner had their accoutn disabled which in turn caused lots of problems. If I remember rightly it wasnt an issue in executing the job as obviously that is under the service account but there were permission issues for sure.
Apologies for no exact but I would give a little planning regarding the owner of objects, and as previous poster, my preference would be to reserve sa for admin duties.
'Only he who wanders finds new paths'
February 25, 2013 at 10:50 am
I agree with that 100%. The owner should be a service account that can only access the resources it needs to fulfil its job.
For me personally, the use of the 'sa' account is a huge no-go!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply