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.
+2
A:
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
John Nolan
2009-05-14 19:37:32
When I attempt to delete the user I get... TITLE: Microsoft SQL Server Management StudioDrop failed for User 'domain\username'. (Microsoft.SqlServer.Smo)For help, click: http://bit.ly/gHA9ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)For help, click: http://bit.ly/2lzKpz
Jason
2009-05-14 19:47:12
I imagine there is a schema called 'jason' you'll need to change object from that schema to it. http://msdn.microsoft.com/en-us/library/ms173423.aspx
John Nolan
2009-05-14 20:08:29
I removed the schema and was able to remove the user. When I went back to the diagram, the owner then got changed to public. Still not what I want. Any more suggestions?
Jason
2009-05-15 19:36:00
A:
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.
Jason
2009-05-19 12:44:54
A:
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...
CraigD
2009-05-26 10:28:00