Modifying User Privileges

[JD1016]

Hello,

I recently used ArcGIS Pro to Create an Enterprise Geodatabase.  To this new enterprise geodatabase, I used Create Database User coupled with Create Role to add users and establish an editor_role and a viewer_role respectively. All my users have been successfully added and verified Properties such that those with viewer_role privileges can only SELECT while those with editor_role privileges can SELECT, INSERT, UPDATE and DELETE.

The one issue I am seeing is that those who have viewer_role privileges can still create new feature datasets and import feature classes. 

Is there a way I can remove that ability?

I have read that the Create Database User through Pro creates this setting, whereas if you were adding the user through SSMS, leaving the Default schema blank prevents someone from creating new feature classes and the like.  I'm not really super familiar with SSMS, but if there is something in there that I can use to modify the privileges, please let me know that as well.

Thank you.

JD

[MarceloMarques]

@JD1016 - see my database guide book for Production Mapping, the best practices can be applied to any geodatabase in any industry, you will see how to setup the data owner user, the roles, and created editor and viewer users, and the editor/viewer users cannot create new featureclasses. 

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

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

In the link above you can also download my database tempate scripts for SQL Server, this includes the database guide book best practices and a lot more.

Note, editor / viewer users can still create geodatabase domains and featuredatasets, this is well known for a long time, and an enhancement request was submitted in the past, ENH-000152620, if possible, please you can open a ticket with esri tech support to request the same enhancement, if more customers ask for the enhancement, then it increases the changes for the enhancement to get implemented. There is nothing we can do in the SQL Server database in terms of privileges to prevent that, it is a change that needs to happen in the ArcGIS client software code. 

I hope this helps.

[JD1016 · Accepted solution]

Thank you for the information Marcelo.

I actually ended up doing the following that solved my issue.

In ArcGIS Pro, I created my enterprise geodatabase, created database users (just the editors), and created roles for both editors and viewers.

I then went into SSMS, created logins and users for my viewers only, leaving the default schema blank for all viewers, and pointed them to the viewer role I created in Pro under the Memberships section.

Using this workflow, viewers have only SELECT privileges.  They can add a feature dataset, however, they cannot import nor create a new feature class.  Basically, they have a feature dataset without anything in it.  This is not exactly ideal, however, it does what I need to prevent lower level participants from getting into undesired mischief.

Thank you.

JD

[MarceloMarques]

@JD1016 - I am glad to know that you fixed the issue. Regards, Marcelo  : )