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?

[JackZHANG]

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'

GO

[TimMinter]

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