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
