Most people prefer to have “sa” account as the database owner, primary reason being sa login cannot be removed/deleted unlike any user account or service account and so the databases will never end-up in an orphaned stage.
I came-up with the below method to change the ownership to sa on all the 40 databases in our environment recently.
Step 1: Check the databases that does not have sa account as the owner
SELECT name AS DBName, suser_sname(owner_sid) AS DBOwner FROM sys.databases WHERE suser_sname(owner_sid) <> 'sa'
Step 2: Generate the scripts to make sa account as owner for all the databases
SELECT 'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(name) + ' TO [sa];'
from sys.databases
where name not in ('master', 'model', 'tempdb', 'msdb')
AND suser_sname(owner_sid) <> 'sa'
Step 3: Execute the result set from step 2 above to change the ownership to sa
--Sample result set from step2 above ALTER AUTHORIZATION ON DATABASE::[AdventureWorksLT2012] TO [sa]; ALTER AUTHORIZATION ON DATABASE::[Northwind] TO [sa]; ALTER AUTHORIZATION ON DATABASE::[Pubs] TO [sa];
For more information on sa account you can check my previous blog post HERE
Hareesh G
Jayapal Vajrala
Suresh Raavi
DataGinger.com 
P.56. Resistência Pública, 2009. PAU-BRASIL, MINISTERIO DA SAUDE. http://sakuragaworld.com/modules/wordpress4/wp-ktai.php?view=redir&url=http://tinyurl.com/j6a6rmt
It’s awesome to pay a visit this web site and reading the
views of all friends concerning this piece of writing, while I am also zealous of
getting know-how.
I don’t even know the way I stopped up here, but I believed this put up used to be great.
I do not know who you’re however certainly you’re going
to a famous blogger if you happen to are not already.
Cheers!
You ought to take part in a contest for one of the
greatest websites on the web. I’m going to highly recommend this site!
That is a good tip particularly to those new to the blogosphere.
Brief but very precise info… Many thanks for sharing this one.
A must read post!
I think this is one of the most important information for me.
And i am satisfied studying your article.
However want to commentary on few basic issues, The site style is great, the articles
is actually nice : D. Just right process, cheers
It’s going to be ending of mine day, except before ending I
am reading this wonderful paragraph to increase my know-how.
wonderful issues altogether, you just gained a emblem new reader.
What would you recommend in regards to your publish that
you made a few days ago? Any positive?
Hello, its pleasant post regarding media print, we all be aware of media
is a enormous source of data.
I feel that is one of the most significant information for me.
And i am glad studying your article. However should commentary on some normal issues, The website taste
is great, the articles is actually nice : D. Just right process,
cheers
Thank you for another great post. Where else may anybody get that type of info in such an ideal manner of writing?
I’ve a presentation next week, and I am at the look for
such information.
I do this same action but dynamically:
DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(max)
DECLARE database_cursor CURSOR FOR
SELECT name FROM MASTER.sys.databases where state_desc=’ONLINE’ and User_Access_desc=’MULTI_USER’ and name not in (‘Master’,’Model’,’MSDB’,’TempDB’) and is_read_only=0 and suser_sname(owner_sid) ‘sa’
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Command = ‘
ALTER AUTHORIZATION ON DATABASE::[‘+@DB_Name+’] TO sa
ALTER DATABASE [‘+@DB_Name+’] SET TRUSTWORTHY ON’
–Print @command
EXEC sp_executesql @Command
FETCH NEXT FROM database_cursor INTO @DB_Name
END
CLOSE database_cursor
DEALLOCATE database_cursor
From security stand point and ‘best practice’ I’ll hear suggestions it’s best to disable the ‘sa’ account. Generally when creating the server, I just apply a super complex password to the ‘sa’ account.
Anybody have any thoughts about that?
I think this is among the most vital info
for me. And i am glad reading your article. But should remark on few
general things, The web site style is perfect, the articles
is really excellent : D. Good job, cheers