When creating an ArcGIS Enterprise Geodatabase using the SDE schema in Microsoft SQL Server, should the SDE user be assigned db_owner?

[ifnotthisthanthat]

When you are in the process of creating an enterprise geodatabase using the SDE schema, are you supposed to assign the SDE user the db_owner role?

@LeoDonahue states: "If you choose to go with a sde schema, the sde user will never and should not load data into the geodatabase.  All of your ArcSDE related tables will be prefixed with "sde."  But you are free to create other SQL Server accounts with the correct permissions to perform the role of data loading"

Therefore, because sde is created to solely create the enterprise geodatabase, should the db_owner be assigned to a trusted user?

This article states: "The sde user must be added to the db_owner role in the database to upgrade. 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".

Is it good practice to use sde to set up the enterprise geodatabase and then assign users privileges?

Thanks!

[George_Thompson]

I would not assign db_owner to the SDE (geodatabase admin) user, except when upgrading (as you noted above).

After the enterprise GDB is created, I create data owners and load data / assign permissions / etc. via that user.

[ifnotthisthanthat]

I adopted the workflow where I used SDE to create the enterprise geodatabase, and have assigned the user db_owner. From what I understand, once the enterprise geodatabase is created, the sde user is irrelevant (so to speak). Is this on the right track?

[George_Thompson]

First, I would remove db_owner from SDE, until you need to upgrade to a newer version of the geodatabase (i.e., 10.9.1 --> 11.x).

The SDE user is important to the geodatabase as it owns / maintains the repository tables.

Depending on your workflows, it may be necessary to run some admin tools (rebuild indexes / analyze statistics / reconcile & posting / compression / etc.) as the SDE user. 

I would just not provide the login credentials to other users and keep it for when needed.

[ifnotthisthanthat]

Thank you for this discussion, @George_Thompson. May I ask, why would you give db_owner to SDE when it is time to upgrade the geodatabase? Currently I have assigned db_owner to myself, under username: main_db_user. When it comes to upgrading the geodatabase to a newer version, I was planning on leaving main_db_user as db_owner and upgrading the geodatabase through that user. 

I still plan to run admin tools using the SDE user, but only when it comes to admin processes; otherwise, I plan to use main_db_user for everything else.

Does this sound okay?

[George_Thompson]

Good questions:

May I ask, why would you give db_owner to SDE when it is time to upgrade the geodatabase?

---

This permission is required to successfully upgrade the Enterprise Geodatabase components. Here is the documentation; Step #5 (https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/upgrade-geodatabas...) & permissions for upgrading (https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/privileges-sqlserv...)

I would NOT assign db_owner role to your user. It could cause some permission issues or other weird behavior. There is no need to have it assigned to your user.

I would only use the SDE user to run the upgrade.

Hope that clears up any confusion. Please let me know if you have any other questions. Thanks!

[ifnotthisthanthat]

Interesting you say that you would not assign a user db_owner, because the ESRI documentation says: "Alternatively, the upgrade can be run in SQL Server by a user (other than sde) who is in the db_owner database role or sysadmin fixed server role. This does not apply to database services." Found here.

I assigned myself db_owner because I am going to be controlling the database, its users, permissions, etc. That way I can add users/assign permissions, assign roles, etc, all without having to sign into the SDE user. 

So while yes, the sde user is the geodatabase administrator and is used to admin the geodatabase, the db_owner is on my user account giving me full control of the database.

Also, in step 5, sysadmin is different from db_owner, if that is what you are talking about.

[George_Thompson]

I see what you are saying. Here is my take on this:

- I only use the SDE for geodatabase admin tasks and upgrading. I do not grant permissions via the SDE user.

- Data owners (i.e. GIS / DATA / etc.) have the needed permissions for the data sets they own per documentation. These users provide the editing / viewing permissions to the roles / users as needed.

I usually do not grant db_owner / sysadmin to other users as to not create some permissions headaches and / or really weird behavior.

I am not sure I have run the upgrade process without the SDE (or DBO) user before.

[ifnotthisthanthat]

I think both ways are completely valid.

I took the approach where I used the sde user solely to create the enterprise geodatabase.

Once created, I am putting the database in the hands of a user that I created/own.

Per Esri, "If the geodatabase administrator is a user named sde and that user will not own data in the geodatabase outside of the system objects, the sde user only needs to be able to connect to the geodatabase once the geodatabase has been created."

Therefore, a user can be assigned privileges that represent geodatabase administrator, as well as have full control in terms of users, permissions, etc, by being the db_owner within SQL Server Management Studio.

Thank you for all of your time and input, @George_Thompson!