Cannot delete FC in a Feature Dataset

gisarchitect

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!

gisarchitect

@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.

View solution in original post

gisarchitect

@VinceAngelo @George_Thompson Your support is highly appreciated

VinceAngelo

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

gisarchitect

@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.

George_Thompson

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.

--- George T.

gisarchitect

@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.

George_Thompson

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?

--- George T.

gisarchitect

@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?

George_Thompson

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.

--- George T.

gisarchitect

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?