MSSQL Database Roles to make Schema Changes on a single Database but not on others?

[CHedger]

I have a rather specific MSSQL database question. I have a user that i want to give them the ability to make schema changes to just one database of the 15 that I have. What are the Server Roles and or database roles that I need to give them to allow this? 

The user has been created, and I shared them with the database in question. From there i am not sure what combinations of Server Roles and Database role i need so they cannot have schema changing roles to all databases only for the one database i want. 

[MarceloMarques · Accepted solution]

First let's cover the types of SQL Server Geodatabases.
------------------------------------------------------------------------------

Create a geodatabase in SQL Server—ArcGIS Pro | Documentation
Choose the schema that will contain the geodatabase.

• If you want a user named sde to be the geodatabase administrator and the geodatabase to be stored in the sde user's schema, check Sde Owned Schema.
  
  
• If you want the dbo user to be the geodatabase administrator and the geodatabase to be stored in the dbo schema, uncheck Sde Owned Schema.

For more information on storing the geodatabase in either the sde or dbo schema, see Comparison of geodatabase owners in SQL Server.

------------------------------------------------------------------------------

DBO schema geodatabase repository

The only way that someone is DBO in a database is sysadmin membership, or explicitly ownership of the actual database. Putting someone in the db_owner role confers a high level of permission (similar to DBO), but the login will not be associated with the DBO user. Certain operations will look to see that the name of the connected user matches the name listed as the 'owner' of the table. If they are not a match, the operation will fail.

Example: user A that is sysadmin can perform schema changes in the geodatabase but user B cannot, solution in this case is to grant user B the sysadmin role, but this is not desirable because sysadmin is the highest privilege in a SQL Server Instance. 

------------------------------------------------------------------------------

SDE schema geodatabase repository.

the sde user owns the geodatabase repository tables, and we do not use the sde user to load gis data, instead we create a data owner user to load the data (featureclasses, tables, etc.). Only the data owner user will be able to make schema changes, no other user is allowed to make schema changes.

Caveat, if you grant sysadmin role or if you grant "db_owner" role in the database to the other user then that user shall be able to perform the geodatabase schema changes. Note, you can try to be more specific by granting "alter table ... alter procedure ... etc.). But still ArcGIS Pro might not like the fact that the connected user is not the data owner user and will not let you make the schema change.

I recommend to all my sql server customers to not use "dbo" and instead use an "sde" user to create the sde repository tables and create data owner users, then after the schema is loaded with the data owner user and registered as versioned, archiving enabled, editor tracking enabled, etc, then if working with traditional versioning you can create an editor and viewer user, the editor users have select, insert, update, execute permission on the data owner user tables, and the viewer users have only select permission on the data owner user tables. This is a better implementation in terms of security.

I also recommend to have only the data owner user to make schema changes, best practice, this avoids issues and you have more control over the schema, also to make schema changes all users must be disconnected from the geodatabase and all ArcGIS Server Services that connect to the geodatabase must be stopped, this will remove the locks and then you can connect as the data owner user to make the schema change, but do not click on any other geodatabase connection file for other users, because if any other user is connected to the geodatabase will cause locks and you will not be able to perform the schema change.

------------------------------------------------------------------------------

Geodatabase schema changes shall always be performed with ArcGIS Pro and never directly in the database using SQL or you can corrupt the geodatabase, this because the information about the schema is also stored in the arcsde geodatabase repository tables.

------------------------------------------------------------------------------

More best practices visit my blog post below.

Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices

Database Connections Best Practices

The Production Mapping database guide books are generic and can be applied to any industry.

------------------------------------------------------------------------------

I hope this helps.

[CHedger]

thank you for the detailed explanation

[AndreaB_]

Hi @MarceloMarques ,

I'm hoping you can answer my questions - 

I'm setting up a new SQL geodatabase. I have an 'sde' user which I used that connection to run the Enable Enterprise Geodatabase tool in ArcGIS Pro. I have a new user, 'gis', which I want to act as the data owner. When I try to import feature class as 'gis' I get a permissions error. What SQL database permissions does 'gis' data owner need? It currently has db_datareader and db_datawriter. 

What SQL database permissions does 'sde' need? It currently has db_datareader, db_datawriter, db_ddladmin, Grant: connect, Create function, create procedure, create table, and create view. 

Thank you!

Andrea

[George_Thompson]

I would review the documentation here for the data owner: https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/privileges-sqlserv...

Look at the Data Creator permissions for your 'gis" user and removed the db_datareader & db_datawriter roles.

For the SDE user, here are the permissions: https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/privileges-sqlserv...

I also would remove the db_datareader, db_datawriter, db_ddladmin roles. Those are not needed, based on my experience.

That should get you past the errors.

[AndreaB_]

Hi @George_Thompson , @MarceloMarques 

I followed the permissions you stated. However, now when I try to add the data as the sde user I get: 

SDE must need something more? Thank you.

[George_Thompson]

Can you provide screenshots of the SDE user permissions / settings for the instance and the database?

[AndreaB_]

Hi @George_Thompson ,

Sure thing, thanks. Sorry I didn't think to provide that ahead of time.

I'm not a DBA, just a GIS Professional pretending to be a DBA, lol.

So sde isn't listed as a login to the instance.

Then under the database properties - permissions - sde: it has connect, create function, create procedure, create table, create view

Database User membership: no roles.

[AndreaB_]

Hi @George_Thompson ,

So a couple of things I just learned in my deep dive - sde was missing the 'SELECT' permission so it couldn't add the data to ArcGIS Pro. I added 'SELECT' permission and 'sde' could add the data to Pro. I believe I could just use the db_datareader role for this also.

More interesting - I found this little nugget hidden in here: https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/overview/grant-dataset-privileges.ht...  If the dataset is registered for traditional versioning, the geodatabase administrator must have full privileges on it. I do have traditional versioning, so I am going to assume that my 'sde' user needs 'SELECT, INSERT, UPDATE, DELETE' on all of my data. So that will bring me back around to granting 'sde' the db_datareader and db_datawriter roles.

I see some of this info on that link you gave me before,  https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/privileges-sqlserv... but it wasn't clear to me. 

I also learned from the first link above, "Grant and revoke dataset privileges in databases and enterprise geodatabases", that I could use the Privileges dialog box and the Change Privileges tool in ArcGIS Pro - how awesome! I had no idea. I see the link from one page to the other now. *Deleted the rest that was here...makes sense now...

Thanks for your help.

[MarceloMarques]

@AndreaB_ - you can see how to setup the data owner user and how to set the permissions for the data owner user in my database guidebook for production mapping,  in my database template scripts for SQL Server you will find that information and much more, like how to setup the sde user and how to set the permissions for the sde user, see the link below.

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