Deleting Domains Owned by Others in SQL/SDE Geodatabases

[RandyKreuziger]

We are running ArcSDE 10.1 in a SQL 2012 environment.  I manage the database but I do not own all of the domains.  A couple versions ago the Domains were stored in their own SQL GDB table so it was very easy to change a domains owner through a SQL update script, but now that domains are store in XML (stored in the database) I don't know how to change ownership.

How can you delete in a domain in SQL/SDE that is owned by someone else?

[JohannaKraus1]

We also have a domain created by user account long since deleted.  Have you found a way to delete it?

[RandyKreuziger]

In Microsoft SQL Server you can list your domains via SQL Server with this query

--SQL Server SELECT items.Name AS "Domain Name", items.Definition.value('(/*/Owner)[1]','nvarchar(max)') AS "Owner" FROM dbo.GDB_ITEMS AS items INNER JOIN dbo.GDB_ITEMTYPES AS itemtypes ON items.Type = itemtypes.UUID WHERE itemtypes.Name IN ('Coded Value Domain', 'Range Domain')

You can then construct a SQL delete statement to delete the single domain entry but make sure it's not in use.  This was the only solution I could get from ESRI Tech support last year when I too had this issue.  You need to be able to run the SQL code in Management Studio if you have the rights and be sure to back up your database! 

I've put in an enhancement request a couple of years ago for changing the OWNER of a DOMAIN but I don't believe anything will come of it.

[JohannaKraus1]

Oh wow, thanks.  Everything I read seems to indicate you weren't able to do this, but I'm glad to know we have the option.

Is your enhancement request something to vote on geonet?

[Asrujit_SenGupta]

https://community.esri.com/ideas/5690 

You can "Vote Up" the Idea here!

[JohannaKraus1]

done, thanks!

[RandyKreuziger]

Unfortunately, that ArcGIS Idea was first posted 4 and a half years ago.  I voted it up but the VAST majority of Ideas never get implemented, IMHO.  ESRI is too busy adding functionality and too often breaking things that work in the process.  Don't get me wrong, I've been working with ESRI software 28 years and have no desire to switch!  I just wish there would make more minor enhancements for usability vs new functionality.

[JohannaKraus1]

Agreed!

[DouglasHall]

Further information from anonymous rogue GIS person,

Background: I had domains created by old user accounts and couldn't delete. I setup an Oracle trace and deleted a test domain to see which SDE tables were affected. The trace showed the SDE tables SDE.GDB_ITEMS and sde_xml_doc1 being deleted from.

Find your domains, using queries like above. Here is the online help reference for your type of database: Example: Finding domain owners using SQL—Help | ArcGIS Desktop . I recommend adding the GDB_ITEMS OBJECTID and DEFINITION column to the query. The DEFINITION column will be the key for  deleting in the table sde_xml_doc1. One thing is that the xml magic query for the owner column may not work. For example in Oracle, we don't have the arcgis extproc setup (it's the dll or so file to access shape columns...something like that). You should know which domains to delete even without the owner.

Query for Oracle without the OWNER:

select a.OBJECTID, a.name "DOMAIN_NAME", b.name "TYPE", a.definition
FROM SDE.GDB_ITEMS a INNER JOIN SDE.GDB_ITEMTYPES b ON a.Type = b.UUID
WHERE b.Name IN ('Coded Value Domain', 'Range Domain');

OBJECTID  DOMAIN_NAME    TYPE                      DEFINITION
--------------------------------------------------------------------------------
1921            test_domain          Coded Value Domain     181

So if you would like to risk corrupting the delicate web of SDE, make sure all tables/feature classes are no longer referencing the domain or deleted. Then something like this:

DELETE FROM SDE.GDB_ITEMS WHERE OBJECTID = 1921;
DELETE FROM SDE.sde_xml_doc1 WHERE SDE_XML_ID = 181;

Commit;

Embedded xml domains -- worst idea ever Esri!!!

[RandyKreuziger]

I found a way to change ownership at least in  Microsoft SQL Server using the SET statement with a modify function. In the example below, I change the domain owner of the 'DATA QUALITY CODE_3' to the user 'SDE', but it could be set to any user account regardless of the owner.  At that point I was able to login as SDE and edit the domain as well as delete it.  I was able to figure this out using ESRI's tech article "How To: Return information on coded value domains using ArcSDE 10.x for SQL Server."

UPDATE [GeoLib].[sde].[GDB_ITEMS]
SET [Definition].modify('
replace value of 
(/GPCodedValueDomain2/Owner/text())[1]
with ("SDE")')
WHERE TYPE in ('8C368B12-A12E-4C7E-9638-C9C64E69E98F','C29DA988-8C3E-45F7-8B5C-18E51EE7BEB4')
  AND PhysicalName = 'DATA QUALITY CODE_3'

‍‍‍‍‍‍‍‍