Adding Logins, Users, & Privileges using SSMS & ArcGIS Pro

[JD1016]

Hello,

I'm not quite achieving my goal in setting up users for access to a newly created enterprise geodatabase.  We are using Microsoft SQL Server 19, SQL Server Management Studio v18.12.1(SSMS) and ArcGIS Pro 3.2.2 (Pro).

First my workflow:

1.  Created a new database in SSMS called egdb01.

2.  Opened Pro, added a New Database Connection using Operating System Authentication to egdb01.  Changed the connection name to dbo_egdb01.

3.  Converted the database to a geodatabase using Enable Enterprise Geodatabase in Pro and selecting dbo_egdb01 as the Input Database Connection.

4.  Used Create Database User in Pro, where the Input Database Connection was dbo_egdb01.sde, left Create Operating System Authenticated User unchecked, supplied a Database User name and Password, left the Role blank and Run.

5.  Added a New Connection in Catalog using Database Authentication with the new credentials from #4.  Renamed connection gis_egdb01.sde.

6.  In Pro, I added several new feature datasets to gis_egdb01.sde and import some feature classes to a few of the feature datasets.

7.  Next, I wanted to add a data editor to the geodatabase.  Went back to SSMS, Objects Explorer, expanded Securities folder, RC Logins, New Login.  Added a login name (the first and last name of one my analysts), enabled SQL Server authentication, added a password and confirmed, and set the default database to be egdb01.

8.  Next, I associated the new login with egdb01 by going into Objects Explorer, Databases, egdb01, Security, Users - RC Users and select New User.  User Name - first and last name of my analyst no spaces, Login Name - first and last name of my analyst no spaces, and Default schema the first and last name of my analyst no space so they have the ability to create new feature classes. 

9.  Next, I moved on to Create Roles.  Back to Pro.  Create Role.  Input database connection is dbo_egdb01.sde, Role is editor, and User Name is the first and last name of my analyst no spaces.

10.  Next, I moved on to Change Privileges in Pro.  Input Dataset - I selected all the feature datasets I created, User - editor, View (Select) - Grant view privileges, and Edit (Update/Insert/Delete) - Grant edit privileges.

What's happening:

If my analyst adds the connection using the Database User/Password created in #4, they see all feature datasets and feature classes.

If my analyst adds the connection using #7 credentials, they only see those feature datasets that have feature classes.

Once the connection is added, using either method, they can freely edit without needing any further credentials.

What I would like to happen:

I would like the database connection to be added using their Windows Authentication.

I would also like to have them use database authentication when they are going to edit.

I hope this is clear enough depicting the steps I have taken thus far.  I know I am missing something and hope someone can shine the light in my direction.  Also, if I am not thinking correctly on what I would like, please let me know that as well.

Thank you.

JD

[George_Thompson]

I usually recommend that you have your IT create AD groups for editors / viewers. Then you add those groups to the SQL Instance (via SSMS), map them to the EGDB, and grant edit / view permissions via ArcGIS Pro.

You should not be sharing the data owner connection information with editors as they would have more permissions that needed.

This also does not make you responsible for updating access to their account if IT disables it (i.e., they leave your organization).

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/add-users-sqlserve...

[JD1016]

Our IT is pretty hands off when it comes to anything connected to GIS so this task is up to me.

[MarceloMarques]

@JD1016 - see my database guidebooks for SQL Server in the link below, use the "Production Mapping", the best practices can be applied for any geodatabase. Then, after you read the guidebook you can download my database template for SQL Server. Those will show in detail how to configure the SQL Server Geodatabase and create data owner users, and editor and viewer users, and how to create windows authentication editors and viewers, and how to grant permissions and refresh permissions.

Mapping and Charting Solutions (MCS) Enterprise Da... - Esri Community

I hope this helps.

[ShannonShields]

I've gone through your post a couple of times and need to clarify a couple of things:

1 - When you say I would also like to have them use database authentication when they are going to edit. are you meaning you want them to be able to create new feature classes/tables, or do you want them to edit existing feature classes & tables? 

2 - That first statement seems to sort of contradict this one I would like the database connection to be added using their Windows Authentication. Or are these users not going to create feature classes & tables, but only work with existing ones?

I'd break my users down into 3 categories:

1. Data Creators - these users have elevated privileges that allow them to create feature classes, views & tables in the database. They are the "owner" of those objects and certain operations from ArcGIS must be done as the owner (like granting permission to other users).
2. Data Editors - these users do not create tables, but they have permission to edit data in existing tables & feature classes
3. Data Readers - these users have no permission beyond the ability to read existing tables/feature classes

Are you wanting Data Creators to use a single, database login when they create data so that it all goes on a common schema? But users who will work with that data (editing & reading) will connect using their windows logins?

-Shannon

[JD1016]

Hi Shannon,

Thank you for taking the time to parse through my posting.  Much appreciated.

Database and Windows Authentication Questions:

This was something brought up by our IT department.  They wondered if it was possible to have two authentication types running at the same time.  For instance, when the user first initially creates a New Database Connection...they could use Windows Authentication for that component.  When the user seeks to add or begin to edit a feature class...they would need to use Database Authentication to proceed.  Hope that teases it out a bit.

I've been experimenting with using Pro for everything versus going back and forth between Pro and SSMS.  Here are the steps I've taken:

Create Enterprise Geodatabase - Pro

Database Platform - SQL Server, Instance - our server ID, Database - egdb, Operating System Authentication - Disabled, Database Administrator - sa, Database Administrator Password - supplied, SDE owned Schema - enabled, Geodatabase Administrator Password - supplied, Authorization File - located, and Run.

Load Data - Pro

With egdb.sde, I created three feature datasets and loaded various feature classes into each.

Create Database User - Pro

Input Database Connection - egdb.sde (created above), Database User - Holly, Database Password - supplied, and Run.

Create Role - Pro

Input Database Connection - egdb.sde, Role - editor_role, Grant Role, User Name - Holly, and Run.

Create Database User - Pro

Input Database Connection - egdb.sde, Database User - Dale, Database User Password - supplied, Role - viewer_role, and Run.

Create Role - Pro

Input Database Connection - egdb.sde, Role - viewer_role, Grant Role, User Name - Dale, and Run.

Sharing Feature Datasets and Feature Classes - Pro

I copied each of the three feature datasets from egdb.sde over to a connection I established pretending to be the Holly user.  Checking the privileges, she has been given Select, Insert, Update, and Delete.  That was successful.

However, I can't seem to get the privileges for Dale to only be Select since he is just a viewer.  

I hope you followed all of this.

Thanks.

JD

[MarceloMarques]

@JD1016 - 

Best Practices Production Mapping 3.x Workspace in SQL Server® 

see my database guidebook for SQL Server in the link above, use the "Production Mapping", the best practices can be applied for any geodatabase. Then, after you read the guidebook you can download my database template for SQL Server in the link below. Those will show in detail how to configure the SQL Server Geodatabase and create data owner users, and editor and viewer users, and how to create windows authentication editors and viewers, and how to grant permissions and refresh permissions.

Mapping and Charting Solutions (MCS) Enterprise Da... - Esri Community

[ShannonShields · Accepted solution]

1 - They wondered if it was possible to have two authentication types running at the same time.  For instance, when the user first initially creates a New Database Connection...they could use Windows Authentication for that component.  When the user seeks to add or begin to edit a feature class...they would need to use Database Authentication to proceed. 

I'm not sure if I understand what they are wanting here. When you make a database connection, you can only connect as a single login. The connection is made as an Operation System/Active Directory login, or it is made as a database-managed login. You cannot do both at the same time. You could make a second connection as a different login, but that would be a second connection, and it would be a weird workflow to switch back and forth between different connections during the same session.

I think you need to figure out your workflow - are people creating a lot of new feature classes all the time? Or is there a pretty fixed schema and most people are simply viewing or editing existing data? Once you determine this, then you can figure out who needs the ability to create objects, and who only needs to work with existing data.

2 - When you copy the datasets using the 'Holly' connection, you are getting a new copy of the tables, all owned by Holly, so she has view & edit permission automatically as she created the datasets. 'Dale' cannot see anything because he has not been granted permission to see the data. This is a separate step after the data has been created.

Also, given that you have put Dale in the Viewer role, you don't need to explicitly grant to Dale - you can grant to the Role. But the role has to be granted privileges too. That is the missing step here - by default a new feature class or table is invisible to anyone other than the person who created it. They would need to grant permission to view or edit that table to users or roles.

-Shannon

[JD1016]

Authentication - What you indicated is pretty much what I told our IT.  The mixed authentication would be a little silly and I think they now understand that part of the equation.  We'll be going with a database authentication setup.

Workflow - We don't have a lot of data editors.  And those we do have are what you termed as simply viewing or editing existing data with a pretty fixed schema. On occasion there may be the need to create additional feature classes but rarely.

Holly - I copied the feature datasets over from my connection to her established connection.  This produced the SELECT, INSERT, UPDATE, and DELETE.

Dale - I read that I should be able to right click on the dataset under Holly and modify the privileges by adding the viewer_role user.  Then when I copy this dataset over from her connection to Dale's connection the privileges should now only be SELECT.  However, when I do this it still shows that Dale can do everything that Holly can.  I've done this successfully using feature classes, but for some reason when I use feature datasets I can't get Dale to only have SELECT privileges.

JD

[JD1016]

Shannon,

Your last paragraph shined the light on my dismal attempts and I have it working exactly how I wanted.  I ended up creating the users first and then creating the roles they would eventually be assigned.  Instead of copying the feature dataset from my administrator connection, I modified the privileges of each feature dataset using Add User for editor_role (for Holly with all privileges enabled) and viewer_role (for Dale with only Select enabled).  After refreshing each of their individual connections all is fine in privilege land.

Thanks again.

JD