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

sql-server sql-server-2005 permissions
Even Mien picture Even Mien · Apr 20, 2009 · Viewed 174.2k times · Source

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

Answer

SQLMenace picture SQLMenace · Apr 20, 2009

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

Related questions

Built-in database role in SQL Server 2005 to permit execution of stored procedures?

In SQL Server 2005, there are built in roles: db_datareader db_datawriter etc. Is there any role that lets a user execute an stored proc? I don't want to use db_owner, because that will permit deletion and updates, which …

Read More
SQL Server: permissions to read database diagrams

Could you please advise, what are permissions required to give user access to read Database diagrams in SQL Server 2005? Thanks a lot!

Read More
Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More