Changing DB Owner Script Question

Question

Changing DB Owner Script Question

EMtwo

Hall of Fame

Points: 3498
More actions
September 22, 2014 at 1:15 pm

#311082

Please, if you're familiar with the following script below, can you tell me the logic behind using "SA" login as the database owner for all databases? Is it possible to create a different login account to use for all databases? If so, what privileges should be granted to this login?
USE master
GO
SET NOCOUNT ON
/*Generate an execution script for each database that needs the owner changed*/
DECLARE @LoopExecuteScripts TABLE (exec_command VARCHAR(1000))
INSERT INTO @LoopExecuteScripts
SELECT 'ALTER AUTHORIZATION ON DATABASE::' + name + ' TO sa'
FROM sys.databases
WHERE owner_sid <> 0x01
AND state_desc = 'ONLINE'
/*Loop through each record in @LoopExecuteScripts table and execute the command*/
DECLARE @Current_Record VARCHAR(1000)
SELECT @Current_Record = MIN(exec_command) FROM @LoopExecuteScripts
WHILE @Current_Record IS NOT NULL
BEGIN
PRINT @Current_Record
EXEC (@Current_Record)
--Delete processed record
DELETE FROM @LoopExecuteScripts WHERE exec_command = @Current_Record
--Get next record to be processed
SELECT @Current_Record = MIN(exec_command) FROM @LoopExecuteScripts
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

Just to be clear we are talking about the owner of the database and not a discussion about the dbo role. I use 'sa' as the owner of jobs and databases for the most part. You could create a sql account or Windows account (not a user account) to be the owner, but sa is installed with SQL and I know it will be there. There are some gotchas with this and you should check out the following posts:

http://www.sqlservercentral.com/Forums/Topic758243-146-1.aspx Look at EdVassie's posts

http://www.sqlservercentral.com/Forums/Topic687144-146-1.aspx Look at RBarry Youn's post (last one)

These are old posts, but have good information about database owners.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 2

This could a good start :

Choose an Authentication Mode

the "Connecting Through SQL Server Authentication" section.

Cheers,

Iulian

EMtwo Hall of Fame Points: 3498 More actions · Answer 3

The question is about the owner of the database and not the db_owner role.

Thanks for the response.

patnaseo Valued Member Points: 57 More actions · Answer 4

I will suggest that to counter these kind of questions please visits websites like http://www.techgurulab.com. Where you can get all kind of stuff i.e; Quizzes, Tutorial, Tests and Study Materials. These will help to build basic concepts of your own.

patnaseo Valued Member Points: 57 More actions · Answer 5

I will suggest that to counter these kind of questions please visits websites like www[dot]techgurulab[dot]com. Where you can get all kind of stuff i.e; Quizzes, Tutorial, Tests and Study Materials. These will help to build basic concepts of your own.