How do I change the owner of a SQL Server database

Question

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

User · Answer

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

User · Answer

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

User · Answer

Here is a way to change the owner on ALL DBS  excluding System   EXEC sp msforeachdb  USE     IF        lt  gt    master   AND        lt  gt    model   AND        lt  gt    msdb   AND        lt  gt    tempdb   BEGIN  exec sp changedbowner   sa   END

User · Answer

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

[sql-server] How do I change the owner of a SQL Server database?

Examples related to sql-server

Examples related to sql-server-2005

Examples related to permissions