68

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'?

Even Mien
  • 44,393
  • 43
  • 115
  • 119
  • I don't understand why you think that this has anything to do with the owner? It just wants to know if it should create the objects to make diagrams possible. – Stefan Steinegger Apr 20 '09 at 18:15
  • If I change the owner, then I don't get prompted to create the objects. I'm not sure if they are automatically created afterwards. – Even Mien Apr 20 '09 at 18:28

4 Answers4

106

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.

Alex Aza
  • 76,499
  • 26
  • 155
  • 134
  • 2
    @ilter - not sure I understood your comment. The question was 'What's the syntax for changing the owner of this database to 'sa'?'. I believe I answered this question. If you run this statement DB owner will be changed to sa. Moreover this is recommended syntax to use as the opposite to using older sp_changedbowner that will likely be retired eventually. – Alex Aza Oct 24 '14 at 22:02
  • 1
    I've been at this for 2 days going down every single path for `Could not obtain information about Windows NT group/user 'domain\user', error code 0x5. (Microsoft SQL Server, Error: 15404)`. I never went this route because my db owner was ***already*** sa. – Code Maverick Oct 17 '18 at 22:09
  • After I resolved every single SQL event log error to no avail and removed all traces of said user, I decided that somehow, someway, the user it was barking about ***had*** to be orphaned within the depths of SQL somewhere. Red Gate's SQL Search revelead 0 entries for the user. I was relegated to doing something that was completely counter-intuitive. I ran this command ... and the error was no more; diagrams could be created. ***Take away is this:*** Even if you already have sa as the owner, ***RUN THIS COMMAND*** and save yourself the headache! – Code Maverick Oct 17 '18 at 22:11
98

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

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
  • 12
    As per BOL: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER AUTHORIZATION instead. https://msdn.microsoft.com/en-us/library/ms178630(v=sql.110).aspx – Tim Friesen Apr 27 '15 at 12:31
  • Sorry for necropost and/or if this is too obvious: What prevents any good old user from executing this command and making themselves the sa? – gary Apr 18 '18 at 02:22
  • @gary - only users with the "TAKE OWNERSHIP" on the target database and either "IMPERSONATE" on their login if they have an existing user in the DB, or "CONTROL SERVER". See: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-changedbowner-transact-sql?view=sql-server-2017 – Jason Musgrove Nov 07 '18 at 13:43
4

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.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • 1
    Can you explain why that I am no longer prompted for this after I change the owner? Are the objects automatically created or does SQL Server just stop asking me because my account is no longer the owner? – Even Mien Apr 20 '09 at 18:29
  • In this case, the DB was owned by login that had been dropped. I'm not sure why because "sa" owns all our databases. – gbn Apr 21 '09 at 03:50
1

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
'
thor
  • 21,418
  • 31
  • 87
  • 173
sawsomething
  • 11
  • 2