topic Re: Some questions on ArcGIS Enterprise SQL server best practices. in ArcGIS Enterprise Questions

Some questions on ArcGIS Enterprise SQL server best practices.

MDB_GIS — Fri, 12 May 2023 11:54:01 GMT

I've found myself acting as the database administrator for our organization despite never having any formal database training, so I have a few questions.

What are the best practices as far as user logins on the sql server? We currently have a "GISadmin" login and then a bunch of individual user logins. The GISadmin login has been used as sysadmin and only used for administrative tasks like reconciling versions and database maintenance. However we have recently had an issue trying to create a parcel fabric on the eGDB and I think it has something to do with the GISadmin having sysadmin privileges. Looking around, it seems like it is recommended that sysadmin versions should not own data. Is that correct? If that is the case, who should there be a designated "data_owner" login that owns all data? If so, what server roles and permissions should that login have?
Adding on to the previous question, are there any other user logins that are considered best practices outside of the standard admin login and individual users? If yes, what are they and what is their purpose and what permissions would they need?
Is it better to create users using the ArcGIS Pro tool or in SSMS? I have been using SSMS because, at first, it seemed easier. I could create the login and then map it to all databases at once using the Login>properties>user mapping window and then grant permissions. If I use the arcgis pro tool, I had to run the "Create Database User" tool for each enterprise geodatabase (so in my case 4 times). I recently found out, however, that the arcgis pro tool also creates user schemas in each database, which is not done automatically if you map the user in SSMS. Is there anything else that is missed if you create it using SSMS?
Are there any free/affordable training options for someone out there for someone like me who has no database admin experience but needs to learn it? It would be great if it was geared toward GIS database admin. The problem I'm having is that most sources I have come across don't really touch the GIS side of database administration. I need to find something that will help me better understand the SQL server management side of things while wearing the GIS glasses.

Re: Some questions on ArcGIS Enterprise SQL server best practices.

George_Thompson — Fri, 12 May 2023 12:32:14 GMT

Those are some great questions, let me try to provide some insight on some of them:

Usually I recommend only using the Geodatabase Admin (SDE or maybe DBO) as the geodatabase admin and not own any objects in the Enterprise Geodatabase (EGDB). I usually recommend that you run the GP Create DB Users tool to create the users that need to own data (i.e., gis, data, parcel, water, etc.).
- Just keep the PW secure for the users above 🙂
For editing / viewing, I recommend that you create AD groups and assign the users via that route. This will allow you not have to manage many different user accounts and permissions. You just manage the group for people to edit / view. This works great in SQL Server.
As for training, I would recommend viewing and taking some of the Instructor led courses that we offer. Here is a link to a search I did: www.esri.com/training/Bookmark/P3QNKMNWL
Also some presentations from the last UC: https://uc2022.esri.com/flow/esri/22uc/uc-2022-ps/page/proceedings?search=geodatabase

Let us know if you have more questions. Thanks!

Re: Some questions on ArcGIS Enterprise SQL server best practices.

A_Wyn_Jones — Fri, 12 May 2023 13:40:28 GMT

Just to add to George's answer:

Following the least privilege model is the most recommended approach - creating read-only users to read from specific schemas (Created by the "Create DB Users" tool and selecting a default schema in SMSS) can help segregate data if required.

The default role in SMSS "db_datareader" can be applied which matches permissions with the following documentation:

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/privileges-sqlserver.htm

Re: Some questions on ArcGIS Enterprise SQL server best practices.

MDB_GIS — Fri, 12 May 2023 20:32:34 GMT

Thanks for tips! I would love to take some of those courses, but unfortunately we just don't have the budget for it!

Re: Some questions on ArcGIS Enterprise SQL server best practices.

MDB_GIS — Fri, 12 May 2023 20:42:16 GMT

Thanks Gordon!

Regarding "selecting a default schema in SMSS," what schema would you select? From what I've gathered, dbo is not recommended. We are having some weird issues with that now. Our GISadmin user was previously a sysadmin, so it had the dbo schema applied. But according to ESRI tech support, this can cause problems. In our case we are getting an error when trying to publish our parcel fabric. The message says something along the lines of the connected geodatabase user "GISadmin" is not the branch version dataset's owner. I've done everything that I can think to resolve it but it seems like it being set as sysadmin has just ruined the login, at least for creating a parcel fabric. Tech support thinks we are most likely going to have to just delete the GISadmin user and create a new one. I'm sure this all stems from something I did wrong when setting it up the first time!

Re: Some questions on ArcGIS Enterprise SQL server best practices.

George_Thompson — Mon, 15 May 2023 11:58:12 GMT

If the data is owned by DBO, then the user has to be DBO / Sysadmin to be the data's owner.

This is where using DBO can get tricky. The default schema should be "GISadmin" for that owner, not DBO or something else.

You may need to delete the parcel fabric, create a new data owner (parcel) then reload as that user. It would alleviate some of these issues.

I am not sure there is a simple "fix" for all of this.

Re: Some questions on ArcGIS Enterprise SQL server best practices.

A_Wyn_Jones — Wed, 17 May 2023 13:27:57 GMT

If you have a database user created via ArcGIS Pro called "GISOwner" - this will create a schema in your Geodatabase called GISOwner. Data loaded via the username GISOwner will then be within this schema.

If you'd like to create a read-only user for the data within this schema - Personally, I would create a user within SMSS (called GISReader), assign the default role of "db_datareader" and then assign the default schema to be "GISOwner".

This will allow you to build a database connection in ArcGIS Pro using the credentials for "GISReader" which will have no permissions for loading/modifying the data.

Re: Some questions on ArcGIS Enterprise SQL server best practices.

MDB_GIS — Fri, 19 May 2023 13:17:33 GMT

Ok. So I do have an update but I'm just as confused as ever.

First, I deleted the parcel fabric from the enterprise database.

I used the "create database user" tool to create a new database authenticated user.

I went to SSMS to give that user the "db_owner" role

I then copied the parcel fabric from the file geodatabase to the enterprise geodatabase.

It copied fine, but now that feature dataset is prefixed with "TestGISadmin.ParcelFabric".

I have never seen this before. Every other feature class and dataset is prefixed with "DBO" Did I do something wrong?

I would prefer our enterprise data not be affiliated with a particular user so is there something that I can do to have it copied with the "DBO" prefix instead?

Re: Some questions on ArcGIS Enterprise SQL server best practices.

George_Thompson — Mon, 22 May 2023 11:36:46 GMT

Giving a database user "db_owner" does not make them DBO when loading data. I would expect the data to be loaded as the data owner name.

If you need to have DBO, usually there is a user that has "SYSADMIN" server role.

Having the data associated with a specific user is not a bad practice and is something that we recommend in SDE geodatabases.

Re: Some questions on ArcGIS Enterprise SQL server best practices.

MDB_GIS — Tue, 23 May 2023 20:28:27 GMT

I guess what is confusing to me is that I was under the impression that we were using the DBO schema. I believe that when the Create Enterprise Geodatabase tool was ran, the option "SDE Owned Schema" was left unchecked. Wouldn't that mean that it would be using a dbo schema? Moreover, in the past, whenever I created any feature classes or datasets with my OS authenticated user, it still had DBO appended rather than my user name.

In this particular case, however, several threads like this one, have stated that Parcel Fabric owners can't use the DBO schema, but I've been unable to find anything about this in the official ESRI documentation.

Re: Some questions on ArcGIS Enterprise SQL server best practices.

George_Thompson — Wed, 24 May 2023 18:23:51 GMT

Yes, if SDE Owned Schema is unchecked, it creates a DBO schema EGDB.

When you created data and it showed as DBO.name, then I would guess that you have "SYSADMIN" under your user.

Here is the doc that states it must be a database user:

The feature dataset in an enterprise geodatabase must be from a database connection established as a database authenticated user. This user must have database privileges to create content and cannot be an OS-authenticated, the database administrator, or the geodatabase administrator account. The connected user becomes the parcel fabric owner and administrator.

https://pro.arcgis.com/en/pro-app/latest/help/data/parcel-editing/createaparcelfabric.htm

Hope this helps.

Re: Some questions on ArcGIS Enterprise SQL server best practices.

MDB_GIS — Thu, 25 May 2023 13:21:04 GMT

So does that mean that the parcel fabric dataset will ALWAYS have a username in front of it? Am I interpreting that correctly?

Re: Some questions on ArcGIS Enterprise SQL server best practices.

George_Thompson — Thu, 25 May 2023 14:00:14 GMT

yes, it would be something like "PARCEL.FABRIC" or "<dataowner>.<featuredataset_name>"

Re: Some questions on ArcGIS Enterprise SQL server best practices.

JackHorton — Thu, 05 Oct 2023 00:42:12 GMT

There are two things in play here. It sounds like your geodatabase was created using the DBO schema. That means that all the internal repository tables are owned by DBO, and it has nothing to do with who owns the parcel fabric. When you copied the feature dataset containing your parcel fabric into the geodatabase, if you were connected as a user called TestGISAdmin, then all the tables in the feature dataset will be owned by TestGISAdmin. The names of the tables follow the format of DatabaseName.OwnerName.TableName, so TestGISAdmin would be a part of all the tables created by TestGISAdmin. That is why the recommended practice is to have your enterprise tables be owned by "headless users", not people's actual names.