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.

How to Change Owner of Database in SQL SERVER? - Interview Question of the Week #117 databaseowner-800x507

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)

, , ,
Previous Post
How to Find Median in SQL Server? – Interview Question of the Week #116
Next Post
What is the Difference between TRUNCATE and DELETE in SQL Server? – Interview Question of the Week #118

Related Posts

8 Comments. Leave new

  • Pinal, the code doesn’t render properly and there doesn’t appear to be a method 2 (sp_changedbowner is the deprecated one…) –Kevin3NF

    Reply
  • Pinal,

    Great post. Always helpful to see multiple ways of completing a task.

    Building on the approaches you described, I was interested in learning when the method 1 approach (ALTER AUTHORIZATION ON DATABASE …) was first offered in SQL Server.

    I used sp_helptext to see what T-SQL code is used by the sp_changedbowner system SP for several different versions of SQL Server. All SQL versions I reviewed used this newer approach shown in your method 1 in the sp_changedbowner SP (SQL 2005 through SQL 2016). The oldest SQL Server version I tested this with was SQL Server 2005. So it has been around at least that long (12 years or more), and possibly longer.

    In effect, all approaches use the method 1 approach under the covers (directly or indirectly). The deprecated but not yet removed SP sp_changedbowner is retained for interface compatibility in existing scripts. It will be interesting to see how soon Microsoft chooses to remove this SP, as they will also have to change SSMS to generate different code when you script off a DB owner change.

    Scott R.

    Reply
    • I agree Scott. Even though Microsoft recommends using new syntax/T-SQL, their product uses old ones :)

      Reply
  • I am looking for a syntax or query that will first check in a database has DB_Owner has set or not and if not it will use ‘sa’. This has to happen for all databases except for the System Databases. Can you advise a script?

    Reply
  • Hi Pinal,
    Does changing DB owner will cause any outage or lock the tables? Is it ok to change it during the business hours?

    Reply

Leave a Reply

Menu