How to Change Owner of Database in SQL SERVER? – Interview Question of the Week #117

Question: How to Change Owner of Database in SQL SERVER?

Answer: There are three different ways to do this task of changing owner but I preferred the method 1 to do so.

Method 1: (Preferred) 

You can run following script in SQL Server Management Studio and it will change the owner of the database to new owner.

ALTER AUTHORIZATION ON DATABASE::AdventureWorks2014 TO sa;
GO

Method 2: (Deprecated but still works and SSMS use it to create script)

USE [AdventureWorks2014]
GO
EXEC dbo.sp_changedbowner @loginame = N'sa'
GO

Though Microsoft says this method will be deprecated in the future version of SQL Server. I still see SSMS 2016 using this method when you click on “Script Action”. I am sure there will be some path in the future when the method will be replaced by the method 1.

Method 3: Using SSMS

Go to SQL Server Management Studio >> Right Click on the Database >> Go to Properties >> Go to Files and select OWNER.

You can see the following screenshot which describes how to do the same task.

Do let me know when was the last time you needed to change the owner of the database and which method did you use to do so?

Reference: Pinal Dave (http://blog.SQLAuthority.com)