topic sde schema user in Geodatabase Questions

sde schema user

HendrikBritz2 — Tue, 08 Nov 2022 09:20:16 GMT

Hi Community

I have a geodatabase that has a sde schema, I created a user with default to the sde schema, but when that user loads data into the geodatabase via ArcGIS Pro 3.0 its shows dbo and not sde.

Any ideas why

Thank you

Re: sde schema user

George_Thompson — Tue, 08 Nov 2022 15:26:22 GMT

Is that user a "sysadmin" or "dbowner" on the SQL instance?

It should create the data in the dataowner's name i.e., data.tablename

Re: sde schema user

MarceloMarques — Wed, 09 Nov 2022 15:56:27 GMT

Create Enterprise Geodatabase

Create a geodatabase in SQL Server—ArcGIS Pro | Documentation

7. 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 A comparison of geodatabase owners in SQL Server.

Best Practices - never load data as the sde user, it is the geodatabase repository owner, instead create a data owner user.

Re: sde schema user

HendrikBritz2 — Wed, 09 Nov 2022 06:25:45 GMT

Hi Marcelo

Your first link is not working

Currently we use the sde user to update the geodatabase.Considering this is not best practice the following scenario:

We created 2 users to update the geodatabase, but when they copy features over to the goedatabase it shows dbo and not sde. If I remove the sysadmin role from the user I get the error "user name and user schema do not match".

See screenshots attached

The data is published to a portal where we created a dashboard for a client.

Re: sde schema user

George_Thompson — Wed, 09 Nov 2022 13:49:39 GMT

Based on my experience when you have "SYSADMIN" it will always load data as "DBO". You need to update the WLA schema to "WLA" and that should allow you have the data correctly owned by that user. That would be following the best practice mentioned above.

Re: sde schema user

MarceloMarques — Wed, 09 Nov 2022 19:11:45 GMT

Create a geodatabase in SQL Server—ArcGIS Pro | Documentation

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

7. 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 A comparison of geodatabase owners in SQL Server.

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

a. check how the sde repository was created dbo or sde , see link above for the documentation

b. check the permissions, follow the documentation
Privileges for geodatabases in SQL Server—ArcGIS Pro | Documentation

c. do not grant sysadmin to the sde user if you are using sde user as the arcsde geodatabase repository owner.

d. (schema1.png picture) the WLA data owner user is not created correctly, it cannot be set to use the sde schema, you must create a schema called WLA and assign that schema as the default schema for the WLA user then grant the proper privileges to the WLA user to be a data owner user, see previous link for the privileges (look for "data creator" = "data owner" user)

e. (Login server roles.png picture) do not grant sysadmin role to the WLA user

f. (Login server roles.png picture) do not grant processadmin role to the WLA user, only the sde user must be assigned this permission

g. read this documentation to understand more about an user that can create data = "data owner user"

Add logins and users to SQL Server—ArcGIS Pro | Documentation

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

You can download my database template scripts and read my database best practices guide books in this link.

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

the database template scripts cover step by step how to setup / configure an enterprise geodatabase in SQL Server in the correct way and shows how to properly create the data owner user and grant the correct permissions, download the database template then make a copy and customize as you need, that is the intention in making those available to customers.

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