topic Re: Deleting Domains Owned by Others in SQL/SDE Geodatabases in Data Management Questions

Deleting Domains Owned by Others in SQL/SDE Geodatabases

RandyKreuziger — Tue, 11 Aug 2015 16:28:36 GMT

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?

Re: Deleting Domains Owned by Others in SQL/SDE Geodatabases

JohannaKraus1 — Mon, 12 Dec 2016 19:30:20 GMT

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

Re: Deleting Domains Owned by Others in SQL/SDE Geodatabases

RandyKreuziger — Mon, 12 Dec 2016 21:23:11 GMT

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.

Re: Deleting Domains Owned by Others in SQL/SDE Geodatabases

JohannaKraus1 — Mon, 12 Dec 2016 21:31:45 GMT

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?

Re: Deleting Domains Owned by Others in SQL/SDE Geodatabases

Asrujit_SenGupta — Tue, 22 Jun 2021 08:20:41 GMT

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

You can "Vote Up" the Idea here!

Re: Deleting Domains Owned by Others in SQL/SDE Geodatabases

JohannaKraus1 — Tue, 13 Dec 2016 18:17:11 GMT

done, thanks!

Re: Deleting Domains Owned by Others in SQL/SDE Geodatabases

RandyKreuziger — Tue, 13 Dec 2016 18:25:41 GMT

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.

Re: Deleting Domains Owned by Others in SQL/SDE Geodatabases

JohannaKraus1 — Tue, 13 Dec 2016 18:29:47 GMT

Agreed!

Re: Deleting Domains Owned by Others in SQL/SDE Geodatabases

DouglasHall — Fri, 21 Jul 2017 22:35:38 GMT

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!!!

Re: Deleting Domains Owned by Others in SQL/SDE Geodatabases

RandyKreuziger — Sat, 11 Dec 2021 06:55:53 GMT

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'

‍‍‍‍‍‍‍‍

Re: Deleting Domains Owned by Others in SQL/SDE Geodatabases

JackZHANG — Mon, 04 Dec 2017 01:53:21 GMT

Thanks Randy, found it very useful and works really well.

UPDATE[sde].[GDB_ITEMS]

SET [Definition].modify('replace value of

(/GPCodedValueDomain2/Owner/text())[1] with ("New_Owner")')

FROM sde.GDB_ITEMS AS items INNER JOIN sde.GDB_ITEMTYPES AS itemtypes

ON items.Type = itemtypes.UUID

WHERE itemtypes.Name IN ('Coded Value Domain', 'Range Domain')

and items.Definition.value('(/GPCodedValueDomain2/Owner/text())[1]','nvarchar(max)') = 'Old_Owner'

Re: Deleting Domains Owned by Others in SQL/SDE Geodatabases

TimMinter — Wed, 06 Jun 2018 18:26:30 GMT

Thanks everyone. the info you shared helped me resolve our similar problem. I then prevented our problem from happening again, and share back in case the info is useful.

When Esri enables or creates a 10.5 enterprise geodatabase in SQL Server, the "Data viewer" "type of user" indicated here (Privileges for geodatabases in SQL Server—Help | ArcGIS Desktop) by default is allowed to create domains and feature datasets, although Esri doesn't mention this situation in the help documentation (as of now, maybe they'll update it, maybe not). These enterprise geodatabase objects get stored in the GDB_ITEMS table in the enterprise geodatabase schema. During configuration, Esri grants delete, insert, and update to the PUBLIC server role. We have our "Data viewer" users in the PUBLIC server role and require that they cannot create anything in our enterprise geodatabases, so I obtained this fix during an Esri technical support request:

--stop allowing "Data viewer" database users to create, update, and delete domains and feature datasets

REVOKE DELETE ON sde.GDB_ITEMS FROM PUBLIC CASCADE;
REVOKE INSERT ON sde.GDB_ITEMS FROM PUBLIC CASCADE;
REVOKE UPDATE ON sde.GDB_ITEMS FROM PUBLIC CASCADE;
GO

--let the "Data creator" database user ("DataCreator" below) or any other user you want continue to create, update, and delete domains and feature datasets
GRANT INSERT ON sde.GDB_ITEMS TO DataCreator;
GRANT UPDATE ON sde.GDB_ITEMS TO DataCreator;
GRANT DELETE ON sde.GDB_ITEMS TO DataCreator;
GO