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

473
2
Jump to solution
01-11-2024 12:02 PM
Labels (3)
CHedger
New Contributor III

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. 

0 Kudos
1 Solution

Accepted Solutions
MarceloMarques
Esri Regular Contributor

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.

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov

View solution in original post

2 Replies
MarceloMarques
Esri Regular Contributor

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.

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov
CHedger
New Contributor III

thank you for the detailed explanation

0 Kudos