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'?
This question is related to
sql-server
sql-server-2005
permissions
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.
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
'
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.
Source: Stackoverflow.com