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)
12 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
Thank you! I fixed the formatted issue.
Yeah it is deprecated. I totally agree.
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.
I agree Scott. Even though Microsoft recommends using new syntax/T-SQL, their product uses old ones :)
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?
Hi Pinal,
Does changing DB owner will cause any outage or lock the tables? Is it ok to change it during the business hours?
correction: Does it lock the Databases when you change the DB Owner?
Can we rename system databases owner sa to another name please suggest me.
Can we change system databases owner from sa to another name? please advise.
Using method 3, after your screen shot is a dialog asking for the new owner’s account. That dialog is not recognizing several names from our domain, including the one I’m trying to assign ownership to. That dialog has a “Object Types” button but only one type is available “Logins” instead of the usual Groups,. Users, et al — Using the “Browse” button shows a list of 38 items — there are over 2,000 accounts in the AD domain. *Some* domain users appear, but very few, and not the domain user I need to assign ownership to.
Any ideas where to go from here???
Hello,
Is there a way to modify db_owner for several db at the same time?
Have a nice day,
Florent