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!

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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!

Find more posts tagged with

Accepted answers

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.

All comments

gisarchitect

@VinceAngelo @George_Thompson Your support is highly appreciated

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.

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.

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?

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.

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?

George_Thompson

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.

gisarchitect

@George_Thompson

Thank you for your assistance.

George_Thompson

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

gisarchitect

@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

George_Thompson

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?

gisarchitect

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.

George_Thompson

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

MarlonAmaya

Hi @gisarchitect ,

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

Marlon

MarlonAmaya

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

Marlon

gisarchitect

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

gisarchitect

Does the user you used follow this pattern "domain\username"?

MarlonAmaya

Yes it does.

Is the behavior reproduced with another OS account that can be tested by a colleague?

Is the .....\GISAdmin OS account an individual account for one user? Or does it act like a group?

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/add-users-sqlserver.htm#:~:text=privileges%20for%20instructions.-,Create%20logins%20and%20users%20with%20SQL%20Server%20tools,-If%20you%20want

gisarchitect

Yes, the behavior can be reproduced with another OS account. I have tested another account of my colleague and the issue appeared again.

The GISAdmin is an individual account not a group. Also the account I have just tested is an individual account.

The scenario I follow is

1.Create OS login (search for login in active directory)

2.Map the login to a certain database

3. Create a schema with the same name the user that is created in the database after mapping.

4. Assign the schema to the user

5. Grant the permission from SQL Server (Table, view, procedure)

6.Connect from ArcGIS Pro and start creating data (Feature dataset, and feature class)

7. Try to delete a feature class

MarlonAmaya

Hi @gisarchitect ,

Looking at the earlier post and recent one, are you assigning Create Function?

If we were to go and check the explicit permission on the user (Right Click DB --> properties --> select permissions page --> select user account)

My explicit permissions are the following:

Connect
Create Function
Create Procedure
Create Table
Create View
View Definition

Marlon

gisarchitect

I have checked my explicit permissions and they were as the documentation as follows

Connect
Create Procedure
Create Table
Create View

However, I have added the remaining as you described above which are (View Definition, Create Function) and there is no effect after I added them.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.