How Can Non-GIS Users Upgrade Geodatabase

Question

The SQL Server Geodatabase Privileges help page states that "Alternatively, the upgrade can be run by a user (other than sde) who is in the db_owner database role or sysadmin fixed server role."

My organization has a team of database administrators who typically manage our databases.  If they were to upgrade the geodatabase, am I correct in understanding that they would need access to desktop GIS software (ArcMap or ArcGIS Pro) to complete this task?

Anonymous User · Answer

Yes, that is correct. In theory, they could RDP into a PC with ArcGIS Desktop and run geoprocessing tool.

They would also need an ArcGIS Server License File to create an enterprise geodatabase. I always use sa account to eliminate any unforeseen issues. Lastly Remove all locks/user connections before proceeding.

This will get any orphan locks:

Check for orphaned locks from the SQL Server management studio by running the below query:
SELECT * FROM DBO.SDE_table_locks ;
SELECT * FROM DBO.SDE_layer_locks ;
SELECT * FROM DBO.SDE_object_locks;
SELECT * FROM DBO.SDE_state_locks ;

below query deletes all the orphaned locks from the SQL Server management studio:
DELETE FROM DBO.SDE_table_locks ;
DELETE FROM DBO.SDE_layer_locks ;
DELETE FROM DBO.SDE_object_locks ;
DELETE FROM DBO.SDE_state_locks ;

George_Thompson · Answer

I would say yes. They would need access to an ArcGIS client to run the upgrade process. This could be done via ArcGIS Pro or ArcPy scripting.

The client is the easiest way to upgrade.

My recommendation is to have them temporarily grant the SDE user the db_owner permissions, run the upgrade tool, then remove that permission.

The upgrade should only take a few minutes (if that).

First and foremost, make sure that you have taken a verified backup of the SQL DB. & follow all the documentation and steps online.