nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Najděte více příspěvků označených jako

Editing

Database Access

Databases

Accepted answers

MMBR

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.

All comments

MMBR

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

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

SDE schema geodatabase repository.

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

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-sqlserver.htm#ESRI_SECTION1_C1C15319C6AE40B8993FDB2E89482D5F

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-sqlserver.htm#ESRI_SECTION1_E5B409382753421284619F35ABF001A3

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.

MMBR

@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

AndreaB_

Thank you so much @George_Thompson and @MarceloMarques !! Exactly what I needed. I will change the permissions.

Follow up question: I would like to add 'sde' to the processadmin fixed server role and grant the VIEW DEFINITION privilege on the database to drop geodatabase connections. (as per Esri documentation). I am getting push back from the DBA and they want to try lower permissions first to see if we can accomplish dropping geodatabase connections in ArcGIS Pro without that high of level of role and privileges. Do you have any advice for me on that?

Thank you!

George_Thompson

I am not aware of another way to drop the connections without the processadmin fixed server role.

MMBR

@AndreaB_ - lower permission will not work, for the sde user to be able to drop geodatabase connections the sde user needs to be granted processadmin privilege.

--Allows the sde user to kill connections to the geodatabase
EXEC master..sp_addsrvrolemember @loginame = N'sde', @rolename = N'processadmin'
GO

Privileges for geodatabases in SQL Server—ArcGIS Pro | Documentation

AndreaB_

@George_Thompson @MarceloMarques Thank you for the confirmation!

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.

Připojte se ke konverzaci

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.