SA account can't change feature class schema in ArcGIS Pro

[frasertab]

Hello,

I'm the classic GIS Analyst turned self-taught DBA, so apologies if any of the terms I'm about to use are incorrect.

So, we have a new enterprise geodatabase I'll call the DEV database, and our current database I'll call the LIVE database. LIVE uses dbo ownership, and administrative tasks like schema changes are performed by a SA account called GISDBAdmin. As far as I can tell, GISDBAdmin is not the data owner but is able to make schema changes in ArcGIS Pro. LIVE was set up by someone else so I can only guess how it was set up by looking at the existing settings. DEV was built by me as a sde-owned database, and I created an account called GIS to be the data owner. I also added GISDBAdmin again as an SA account.

The issue is, historically we've performed maintenance during after-hours windows by unchecking the box that allows database connections and deleting the locks. We recheck it when maintenance is finished. This worked fine for LIVE where GISDBAdmin could both disable connections and make schema changes. But with DEV, GISDBAdmin can disable connections but can't edit the schema (despite being an SA), and GIS can edit the schema but can't disable connections. So, it's deadlocked.

I think there might be three possible options but I'm not sure which is the best one (or if they're all even possible):

1) Is there a setting or permission in ArcGIS Pro or SSMS that would let GISDBAdmin edit the schema? This is preferred if we can do it.

2) Alternatively, can (or should) I try to give GIS permission to disable connections? Can I just give GIS that permission or would it have to be part of a larger role change?

3) Is it best practice for us to settle for the disable services method and stop unchecking the disable connections box? I do have our services set up to allow schema changes, but there are still workflows where it's nice to ensure no users can suddenly connect.

Does anyone have any suggestions or advice on the best way to handle this? Thank you!

[SaraHopkins]

@andybelcher_martinmarietta_com Andy question 🙂

[George_Thompson]

I am thinking that for your "LIVE" EGDB that is DBO schema when you are given SA permissions, you "assume" the identity of DBO and can therefore make the changes that you are attempting.

With your "DEV" EGDB (SDE schema), only the table owner (GIS) will be able to make those changes, regardless of permissions.

The data owner usually does not have the permissions to disconnect users, only SDE does: https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/privileges-sqlserv...

I am not 100% sure, but you could try adding the GIS user to the "processadmin fixed server role and granted the VIEW DEFINITION". This is NOT best practice as it may allow additional permissions to the GIS user.

Just for clarification, are you making the schema changes in SSMS or in Pro during this process?

[VinceAngelo]

The point behind using separate development and production databases is to keep dev as close as possible to production. The choice between DBO- and sde-owned geodatabases is somewhat fundamental, and straddling the two models like this is bound to cause this exact sort of problem.

The sde-login owned geodatabase model does not know how to respect DBO rights, so piling on more permissions is not likely to be able to help.

At this point, you need to choose between not locking out non-sde logins in DEV, and reconfiguring the DEV database to be DBO-owned.

I personally hate DBO geodatabases with a passion, but if that's your production configuration, I'd suggest you restart with a new DEV and use that same model.

- V

[frasertab]

I should have clarified in the post, DEV is on a different instance than LIVE and is slated to replace LIVE in the future, which is why we changed the ownership model. But this is good to keep in mind for the future if we add a true dev/test environment again later.

[frasertab]

Hi all,

Thank you for the feedback so far. I realized that I wasn't making sure that the data owner connection file was active in ArcGIS Pro before I disabled the connections. If I make sure it's connected first, and delete all connections except for the data owner, I can make schema changes with the data owner while connections are disabled which solves my problem. We will just need to add that additional step to our procedures.