Select to view content in your preferred language

sde schema user

1876
5
Jump to solution
11-08-2022 01:20 AM
Labels (1)
HendrikBritz2
New Contributor II

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

Tags (3)
0 Kudos
1 Solution

Accepted Solutions
MarceloMarques
Esri Regular Contributor

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.

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

| 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

5 Replies
George_Thompson
Esri Frequent Contributor

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

--- George T.
MarceloMarques
Esri Regular Contributor

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.

| 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
0 Kudos
HendrikBritz2
New Contributor II

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.

0 Kudos
George_Thompson
Esri Frequent Contributor

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.

--- George T.
MarceloMarques
Esri Regular Contributor

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.

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

| 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