How do I change the owner of a SQL Server database?

Sql ServerSql Server-2005Permissions

Sql Server Problem Overview

When I accidentally click on the Database Diagrams tab, I get one of the following errors:

> Database diagram support objects > cannot be installed because this > database does not have a valid owner. > To continue, first use the Files page > of the Database Properties dialog box > or the ALTER AUTHORIZATION statement > to set the database owner to a valid > login, then add the database diagram > support objects.

--- or ---

> The database does not have one or more > of the support objects required to use > database diagramming. Do you wish to > create them?

What's the syntax for changing the owner of this database to 'sa'?

Sql Server Solutions

Solution 1 - Sql Server

To change database owner:

ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa

As of SQL Server 2014 you can still use sp_changedbowner as well, even though Microsoft promised to remove it in the "future" version after SQL Server 2012. They removed it from SQL Server 2014 BOL though.

Solution 2 - Sql Server

to change the object owner try the following

EXEC sp_changedbowner 'sa'

that however is not your problem, to see diagrams the Da Vinci Tools objects have to be created (you will see tables and procs that start with dt_) after that

Solution 3 - Sql Server

This is a prompt to create a bunch of object, such as sp_help_diagram (?), that do not exist.

This should have nothing to do with the owner of the db.

Solution 4 - Sql Server

Here is a way to change the owner on ALL DBS (excluding System)

EXEC sp_msforeachdb'
USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN
 exec sp_changedbowner ''sa''
END
'
Categories
Recommended Sql Server Solutions
Recommended Sql Server-2005 Solutions
Recommended Permissions Solutions

Previous Solution:

C++ getters/setters coding style

C++Coding StyleGetter Setter

Next Solution:

Loop through JSON object List

JavascriptJqueryWeb ServicesJson

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionEven MienView Question on Stackoverflow
Solution 1 - Sql ServerAlex AzaView Answer on Stackoverflow
Solution 2 - Sql ServerSQLMenaceView Answer on Stackoverflow
Solution 3 - Sql ServergbnView Answer on Stackoverflow
Solution 4 - Sql ServersawsomethingView Answer on Stackoverflow