February 11, 2011 at 7:38 am
Hello All,
Thanks for your time and any input or feedback i receive is greatly appreciated.
So i am in this new environment and trying to clean up the situation i have come into. So i was thinking one of the first things i would do is to get the databases owned by 'sa' and start with the very basics. In some cases i have 22 databases on a server and 19 different owners(hehe exxageration but you get the point) i hope. Well after doing much research and trying things in my development area i didn't encounter any problems or setbacks so i decided to move ahead with making changes in production. Well...BACKFIRE!!!!! i change some database owners to 'sa' and only the database owners and all of a sudden i started dropping connection with logins and things of this sort. My question is this... I have always been under the impression that this is a "fairly safe" process and a behind the scenes change that should not effect other aspects of the database. Where did i go wrong in my thinking and If this is the case..meaning if you cant change a databse owner to 'sa' without issues of things breaking what/where should i look to make adjustments before i start making changes to the owner. FYI.. the previous owners are no longer with the company and their respective accts are inactive. Any insight would be GREATLY appreciated.
DHeath
DHeath
February 11, 2011 at 7:44 am
If you are changing the database owner to sa then one effect of this is to remove the original owner's access to the database. You need to make sure that after you change the owner, you give the original owner the appropriate access.
John
February 11, 2011 at 7:49 am
Thanks for that but the original owner is no longer with the company in most aspects and in others the connections that were dropped were NOT the owners in the beginning. I do appreciate your reply as i will note to make sure the original owners are granted access AFTER i make the change. 🙂
DHeath
DHeath
February 11, 2011 at 7:54 am
If the original owner is, for example, a DBA who has left the company, then you shouldn't need to do anything after changing the owner. However, some databases are created automatically during the installation of the application and are therefore likely to be owned by the application service account. It is such databases that you need to be very careful with.
John
February 11, 2011 at 8:50 am
generally speaking there is no need to change the db owner just because the person no longer exists. The database will be perfectly happy to be owned by the original owner forever.
That being said, if the database is restored to another server the database owner will change and perhaps some objects will no longer work correctly (especially service broker) so be advised.
And always, remember the old adagae: If it aint broke don't fix it.
The probability of survival is inversely proportional to the angle of arrival.
February 11, 2011 at 10:27 pm
sturner... if it aint broke dont fix it... i understand where you are coming from but i also like to have my servers in a very organized manner ..structured in all aspects..from owners to how jobs are created...etc. Its long slow process in getting there but once you get there its much easier i have found to track and resolve issues. Plus on a personal note it drives me crazy to see my databases owned by someone that is no longer with the company but thats just me :hehe:
i like my datases in a "library" style setup when you look at them..all in order
DHeath
DHeath
March 1, 2011 at 2:04 am
So is it fixed now, or do you need any more help?
John
March 1, 2011 at 7:52 am
John,
Everything is taken care of and i am using a process that helps me move forward with 'sa' or 'NT Authority/System' as database owners. As you noted earlier about the change to 'sa' will break the previous owners connection, I check into that and make sure i re-establish that connection if its needed (thanks). So i do appreciate you checking back. I do have one slight concern but not sure what the best way to address this database ownership altho issues, i will continue to use 'sa' as my database owners. I try to use a lot of "best practices" that i have learned over the years and thru SSC but this has me perplexed Microsoft says NOT to use 'sa' as owner of all databases http://support.microsoft.com/kb/2183687 as its stated here:
Best practices for database ownership and trust
• Have distinct owners for databases; not all databases should be owned by sa.
• Minimize the number of owners for each database.
• Confer trust selectively.
• Leave the Cross-Database Ownership Chaining setting off unless multiple databases are deployed at a single unit.
• Migrate usage to selective trust instead of using the TRUSTWORTHY property.
So if you or anyone else has a bit of insight it would be GREATLY appreciated. I think 'sa' is about the most consisten and safest way to go but then again i dont know it all and open to learn.
DHeath
DHeath
March 1, 2011 at 8:02 am
I think that the article you mention only refers to databases with TRUSTWORTHY set to ON.
I wouldn't recommend having NT AUTHORITY\SYSTEM as a database owner. If you do, any application that runs under the local system account will have full access to your database. This is also why that account should not be a member of the sysadmin role - but check that SQL Server is not running under the local system account before changing it!
John
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply