topic Re: Cannot delete FC in a Feature Dataset in Geodatabase Questions

Cannot delete FC in a Feature Dataset

gisarchitect — Thu, 27 Jun 2024 11:41:39 GMT

Hello everyone,

I'm experiencing an unusual issue with an Enterprise Geodatabase hosted on SQL Server. Specifically, I'm unable to delete a feature class within a dataset from ArcGIS Pro. When I attempt to delete the feature class, the process appears to complete successfully without any errors. However, after refreshing the feature dataset, the feature class still appears intact. Furthermore, I can add it back to the map without encountering any issues, and the data remains unaffected.

Some other information

I noticed that this behavior only happen when the data owner is not dbo (ex: if the feature class is dataowner.building, then I cannot delete it). On the other hand I can delete a feature class in a feature dataset when it is owned by dbo (ex: if the feature class name is dbo.building, then I can delete it)
Regarding permissions, the schema owner (dataowner) has the permissions listed in the documentation.
I can reproduce the behavior in multiple geodatabases. Every new database I create has the same behavior.
I have tried creating a trace using SQL Server profiler and tried collecting different type of information with no luck. I used a standard trace template and a custom one. No errors have been detected.
I have tried also extended events and no errors also noticed.
I can delete the table (feature class) in SQL Server from SSMS but I know that this is a wrong action as the tables have another dependencies and the table still exist in the geodatabase. I have just mentioned this piece of information just to confirm that the case is not related to permissions.
I cannot delete the table from ArcGIS Pro with a sysadmin user.
I cannot delete the table from ArcGIS Pro as the schema owner even if I assigned the schema owner the sysadmin role

Environment information

The geodatabase is in DBO owned schema
User data is in dataowner schema
SQL Server authentication is (Windows Authentication)
ArcGIS Pro Version is 3.1.3
SQL Server version is 15.0.4345.5
SQL Server Edition: Enterprise
Operating System: Windows Server 2022

Has anyone else encountered this problem or have any suggestions on how to resolve it?

Thank you!

Re: Cannot delete FC in a Feature Dataset

gisarchitect — Fri, 28 Jun 2024 07:28:12 GMT

@VinceAngelo @George_Thompson Your support is highly appreciated

Re: Cannot delete FC in a Feature Dataset

George_Thompson — Fri, 28 Jun 2024 11:24:35 GMT

When you try to delete the feature class. Are you logged in as "dataowner" or as your Windows user, just for clarification?

Can you provide all the permissions that the dataowner has, including any fixed-server and db roles?

What is the username / schema mapping, please provide screenshot.

Re: Cannot delete FC in a Feature Dataset

VinceAngelo — Fri, 28 Jun 2024 15:38:25 GMT

From my About page:

Note: Please do not try to message me directly or tag me in questions; just ask a question in an appropriate community, and if I see it, have something to add, and have the time, I'll respond.

Tagging me in questions is more likely to reduce the time I have to answer, and delay whatever response I might have.

- V

Re: Cannot delete FC in a Feature Dataset

gisarchitect — Sat, 29 Jun 2024 08:02:23 GMT

@VinceAngeloWell noted. I'm just hopped to get help as I cannot reach to technical support as our client is out of service contract and this is a production environment. Thanks for letting me know this policy.

Re: Cannot delete FC in a Feature Dataset

gisarchitect — Sun, 30 Jun 2024 10:14:29 GMT

@George_Thompson

Yes I'm logging with the data owner in my case the data owner name is 'GISAdmin'.
Attached is a screenshot with the permissions assigned
The username/schema mapping is correct. see the screenshot for the mapping.

I do appreciate your reply.

Thank you.

Re: Cannot delete FC in a Feature Dataset

George_Thompson — Mon, 01 Jul 2024 11:06:54 GMT

So, I ran a similar SQL for permissions and the difference I saw, was in the 'principal_type_desc' field. My data owner ("data") is a SQL_USER and not a WINDOWS_USER.

SELECT USER_NAME(dppriper.grantee_principal_id) AS [USERNAME], dppri.type_desc AS principal_type_desc, dppriper.class_desc, OBJECT_NAME(dppriper.major_id) AS object_name, dppriper.permission_name, dppriper.state_desc AS permission_state_desc FROM sys.database_permissions dppriper INNER JOIN sys.database_principals dppri ON dppriper.grantee_principal_id = dppri.principal_id WHERE USER_NAME(dppriper.grantee_principal_id) = 'data'

Can you please see if your login properties match something like this for the GISAdmin user?

Re: Cannot delete FC in a Feature Dataset

gisarchitect — Mon, 01 Jul 2024 11:57:27 GMT

@George_Thompson I have attached the loggin mapping screen. It seems the same as yours.

For the type of loggin (SQL User or Windows) Are there any differences or restrictions in case Windows login is used?

Re: Cannot delete FC in a Feature Dataset

George_Thompson — Mon, 01 Jul 2024 12:30:48 GMT

Not sure if there is a difference between the two......

I would highly recommend reaching out to technical support on this issue. Outside of the SQL vs Windows login, everything seems to be ok. I have also not seen that issue like this.

Re: Cannot delete FC in a Feature Dataset

gisarchitect — Mon, 01 Jul 2024 14:54:17 GMT

This is an update. I have created another geodatabase and dataowner user, and assigned the same privileges but for SQL login not a Windows login and successfully deleted a feature class in a feature dataset without any problems. I think it is clear for me now that the windows login is accused for this issue, but still I am not sure what is the problem with Windows login. Is there any specific permission or considerations should I take regarding the windows login in SQL server and Geodatabase?

Re: Cannot delete FC in a Feature Dataset

George_Thompson — Mon, 01 Jul 2024 16:23:35 GMT

Thanks for the update on your testing. I am not aware of any specific permission required between SQL / Windows logins.

We usually do not recommend that Windows logins own data due to naming and when the person leaves. This is the first time I have come across this difference.

Re: Cannot delete FC in a Feature Dataset

gisarchitect — Mon, 01 Jul 2024 17:09:58 GMT

@George_Thompson

I've almost figured out what is happening with the issue. I created a new SQL user named 'gisadmin' and mapped it to the existing Windows login 'domain\gisadmin'. After doing this, I was able to successfully create and delete a feature class inside a feature dataset without any problems. It seems that the issue was related to the naming convention of the domain\username causing conflicts. This mapping resolved the problem.

Thank you for your assistance.

Re: Cannot delete FC in a Feature Dataset

George_Thompson — Mon, 01 Jul 2024 17:21:07 GMT

Thanks for that update. We usually want the username / login name to match exactly for best outcomes and less issues.

Re: Cannot delete FC in a Feature Dataset

gisarchitect — Mon, 01 Jul 2024 17:26:28 GMT

@George_ThompsonIf you have a time and found that suitable for you could you try to reproduce the issue using a windows login that equal the sql user name? just to report this to Esri.

I do appreciate your help and time.

Thanks

Re: Cannot delete FC in a Feature Dataset

George_Thompson — Mon, 01 Jul 2024 17:29:05 GMT

This is where I recommend working with technical support on this. They have the mechanism to reproduce the issue and the process for logging potential issues.

@MarlonAmaya any thoughts on getting this investigated?

Re: Cannot delete FC in a Feature Dataset

gisarchitect — Mon, 01 Jul 2024 18:25:56 GMT

Thank you for your recommendation. Unfortunately, our client is currently out of the service contract, so we are unable to reach out to technical support.

Re: Cannot delete FC in a Feature Dataset

George_Thompson — Mon, 01 Jul 2024 18:33:52 GMT

Roger, I am not sure how to report an issue outside of support.

Re: Cannot delete FC in a Feature Dataset

MarlonAmaya — Mon, 01 Jul 2024 20:16:55 GMT

Hi @gisarchitect ,

What account is your data owner? Is this an OS user?

Marlon

Re: Cannot delete FC in a Feature Dataset

MarlonAmaya — Mon, 01 Jul 2024 20:27:41 GMT

I created a sample feature dataset that is owned by OS user , created FC, deleted FC - FC is deleted successfully.

Marlon

Re: Cannot delete FC in a Feature Dataset

gisarchitect — Tue, 02 Jul 2024 05:36:29 GMT

Yes, the data owner account is OS (windows) user