CODEWITHSUNDEEP
sql-serversql-server-2005
Jason
Jason

Reputation: 17976

Change Owner of Database Diagram in SQL Server 2005

I need to change the owner of a database diagram in SQL Server 2005. Currently it is owned by me (domain\username.diagramName) and I would like to change it to be owned by dbo (dbo.diagramName). I thought I could use sp_changeobjectowner, but I believe that is only for tables, stored procedures, etc... I think this is pretty easy, I just haven't been able to find anything through a google search.

Upvotes: 15

Views: 11022

Answers (5)

hamed aj
hamed aj

Reputation: 2020

USE [db_name]
UPDATE dbo.sysdiagrams 
SET principal_id = 1

if above statement not work, you can take an edit on this table with right click on the dbo.sysdiagrams and select Edit top 200 rows, then you can set principal_id column for all records manually set to 1

Upvotes: 5

Andrew H
Andrew H

Reputation: 161

UPDATE dbo.sysdiagrams 
SET principal_id = 1

Set a WHERE clause if needs be. The text above suggests you need to remove the user from the database, but I didn't have to do that. I'm using SQL2008 R2.

Upvotes: 16

Conceptdev
Conceptdev

Reputation: 3261

Although it wouldn't have helped in this case (because it just re-creates the entire sysdiagrams row), these scripts

ScriptDiagram2005 or ScriptDiagram2008

allow you to create an INSERT script of the diagrams 'content', to be re-run against the source database (or a backup, or scripted copy, or whatever). You could easily (knowing what you now know about principal_id) re-create the diagram with a new owner as well.

Just find and edit the rest of this statement in the script

INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition])

the real benefit is being able to source-control/backup the text file...

Upvotes: 0

Jason
Jason

Reputation: 17976

In the end, I had to remove that users schema then run the following update... 

-- to identify the diagram id, name, & owner

select *
from dbo.sysdiagrams


-- to manually change the owner of a diagram
-- note: i derived that the principal_id =1 for dbo from above query

update 
dbo.sysdiagrams set principal_id =1 where diagram_id = 1

I ended up getting the help I needed from a DBA here on campus.

Upvotes: 21

Johnno Nolan
Johnno Nolan

Reputation: 29659

This article states you will have to remove yourself as a user of the db. The open it up with the user of your choice.

http://msdn.microsoft.com/en-us/library/ms171972(VS.80).aspx

Upvotes: 2

Related Questions