Question: How do I change the owner of a database in SQL Server?
Answer: I still prefer the T-SQL method from the original post: ALTER AUTHORIZATION. Choose an appropriate existing login for your environment and run the change only when you intend to transfer ownership. The old post also showed a deprecated procedure and the SSMS Owner field. Here are all three, with the modern command first.
Preferred: ALTER AUTHORIZATION
ALTER AUTHORIZATION ON DATABASE::AdventureWorks2014 TO sa;This uses the original article’s example database and login. Substitute the database and approved owner login for your system. Check the owner before and after with a read-only query:
SELECT name, SUSER_SNAME(owner_sid) AS DatabaseOwner
FROM sys.databases
WHERE name = N'AdventureWorks2014';Changing an owner is a security decision. It needs suitable permissions and can affect ownership chains and access, so review the chosen login rather than copying sa without thought.
The older procedure
The original article also showed sp_changedbowner:
USE AdventureWorks2014;
EXEC sys.sp_changedbowner @loginame = N'sa';Microsoft now says this procedure will be removed in a future SQL Server version and recommends ALTER AUTHORIZATION for new work. I retain it here so older scripts remain understandable, not as the recommended new command.
Where SSMS shows the owner
In SSMS, right-click the database, open Properties, and look for the Owner field. This is the useful part of the original SSMS screenshot, cropped to the field so the owner is readable. It shows an older SSMS interface, so current dialogs may look different.
Which method did you last use to change a database owner, and why was the change needed?
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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