Migrating from 10.9.1 to 11.5 and Updating Permissions/Roles to Best Practices

[AprilChipman]

I am in the process of migrating/upgrading our single server Enterprise 10.9.1 on Windows Server 2016 software to a multi-machine Enterprise 11.5 installed on Windows Server 2025. While doing so, I'd like to update our SDE connections to 'best practices' (as I understand them - it's all a bit daunting).  We have SQL Server 16 on a Windows Server 2022 Datacenter. There are 3 full-time GIS users who access the EGDBs regularly. All of us need the ability to read, write, and publish services from the EGDBs. Currently, we all access the 10.9.1 EGDBs as SDEADMIN.

My thought process is that I should create new EGDBs at the 11.5 version using the SDEADMIN and then run the Create Database User tool to add the 3 GIS users. Then my GIS Team can connect to the new EGDBs as individuals instead of sharing the SDEADMIN account.

But, that isn't working for me...  😞

I've created an EGDB, created the Database User, and can connect to the EGDB using OS authentication. But then my users can't add or edit items in the EGDB. I poked around enough to figure out how to add each user to a feature class and give them privileges, but I've got nearly 500 feature classes to migrate to the new EGDB. No one has the time or patience to edit those privileges on each FC every time one is added, let alone to all of them at once.

Is there a way to set permissions/privileges at the EGDB level? 

Does anyone have a handy flow chart of how the 'best practices' work together? Or an explanatory video? I've got so many tabs open right now I'm completely lost. It took me three hours to find this group!

Any help would be *greatly* appreciated!

[Joshua-Young]

With SQL Server the account hierarchy is the SQL Administrator account (can create databases), then the database owner account (can manage the database), and then user accounts (create, edit, or just view tables depending on permissions). Those can all be the same account but that is not the best practice.

This is what I usually do in our environment using SDE schema.

1. My admin Windows account is in the SQL admin group so it can create an enterprise geodatabase (EGDB). For simplicity, I use the Create Enterprise Geodatabase tool to create the EGDB and we use SDE owned schema instead of DBO.
2. Create a database connection to the new EGDB using my admin Windows account (if the accounts already exists in SQL Server, I think this can just be the SDE account instead)
3. Use the database connection in Step 2 to run the Create Database User to create an account to load the data into the EGDB.
4. I use SQL Server Management Studio to create the "regular" user accounts and add them to the EGDB. These accounts can view or edit data but cannot modify or add feature classes or tables. You cannot use the Create Database User tool for this because it gives too many privileges.
5. Create another database connection for the new EGDB using the data loading account
6. Use the database connection in Step 5 to load data into the EGDB
7. After the data is loaded into the EGDB, use the database connection in Step 5 to run the Change Privileges tool. The Change Privileges tool can process multiple feature classes and tables by default.
   • If any of the users have the exact same privileges, you can run Change Privileges in batch mode with the User parameter as the batch parameter.
   • You will have to run the Change Privileges tools each time different privileges are required for different users. Here is an example:
     • John and Jane both view table 1 (Input Dataset: table 1; Users: John, Jane; View: Grant)
     • John only views table 2 (Input Dataset: table 2; Users: John; View: Grant)
     • Jane edits table 2 (Input Dataset: table 2; Users: Jane; View: Grant; Edit: Grant)
8. Now your users can create database connections to the new EGDB and see what has been shared to them.

This is probably a bit excessive, but I like having the separation of account duties for security and quality control. I am the only one that has access to the data loading account, and I only use that account to load and manage feature classes and tables. I do not use it for regular access or editing.

[DavidSolari]

I recommend using one DB user account (either per-database or a global account, whatever fits your org better) then use that to create all user objects and create the connection files used to publish data to the Portal. If someone who isn't authorized to publish needs to work with the data they can either use their Windows account to access data shared with them, or just go through a feature service straight from the Portal. I've found trying to deal with anything more complicated just leads to issues with publishing, servers trying to access the data etc. etc. This is also highly recommended if you want to try out branch versioning as you need to sync up the data owner and the .sde credentials to publish those objects out in general.

[AprilChipman]

I'm still confused. All of my GIS team should be able to see, edit, create new layers, create new data in a layer, and publish map or feature services.

Using the Create Enterprise Geodatabase tool in ArcPro, I create an EGDB using the following credentials:
Operating System Authentication : DATABASE_AUTH
Database Administrator/Password : <our sdeadmin account and password>
SDE Owned Schema : check/yes

After running the tool, I have error messages:
1. Error connecting to database as geodatabase admin
2. Failed to execute (CreateEnterpriseGeodatabase).

I go to the Catalog in ArcPro and choose New Database Connection. Here I can connect to the EGDB I created in the failed tool above. I am using the sdeadmin account.

Now, my SQL Database guy has created a User called 'GIS All' and, using our Active Directory accounts, has added all of the GIS team to that user. This user/group has the following roles in SQL:
1. db_datareader
2. db_datawrite
3. db_owner

If my DB Guy adds user 'GIS All' to the new EGDB which failed to create above, yet still exists and can be connected, will my GIS team be able to connect to that EGDB via Operating system authentication?

If they are able to connect via OS Auth, will they be able to:
1. Add new feature classes
2. See and edit all feature classes that are in and will be added in the future to the EGDB
3. Publish services

Are you sure there isn't a flow chart or video somewhere that helps explain all of this? 🙂