topic DBO vs SDE Schema Visual Diagram correct? in Data Management Questions

DBO vs SDE Schema Visual Diagram correct?

MKF62 — Wed, 31 Mar 2021 21:06:49 GMT

I've been trying to visualize more clearly what the difference is between choosing DBO vs SDE schema when creating an enterprise geodatabase. I was wondering if my diagram below makes sense, if you were to choose an SDE database? Is this a good understanding or what am I missing/what did I mess up on this diagram?

Database administrator = has authority over literally everything, including creation of tables & data. (Is the schema dbo?)

Geodatabase administrator = has authority over all spatial tables/layers. Should only create structure, not load data.

Users = given specific privileges by either the Database or Geodatabase Admin, can perform editing functions. They do not create tables or layers.

I tried to synthesize this information into a diagram from the answer to this question, but I'm worried I'm still misunderstanding something in here: https://community.esri.com/t5/data-management-questions/dbo-or-sde-or-what-schema/td-p/340010

Final result of your SDE database would look like this (with some added tables just for example):

Alternatively, if you chose the DBO schema:

Database Admin & Geodatabase Admin = administration over absolutely everything, including creation of tables, spatial layers, and data.

Users = They can load and edit data, as well as create structural elements of the database, just like the admins. Confused here: do they have their own schema or is their schema dbo?

Final result of your DBO database could look something like this:

Re: DBO vs SDE Schema Visual Diagram correct?

George_Thompson — Fri, 02 Apr 2021 19:25:01 GMT

I think that you are on the correct path. Just remember that with the SDE schema you would want another user (data owner) to own those tables. It would look like this:

GIS.Table

GIS.Cities

Here is some documentation to help explain the differences: A comparison of geodatabase owners in SQL Server—ArcMap | Documentation (arcgis.com)

Re: DBO vs SDE Schema Visual Diagram correct?

MKF62 — Tue, 06 Apr 2021 17:08:27 GMT

Ugh. Out all the concepts involved with ArcGIS, this is the one that has always confused me the most and I never seem to gain any clarity on it. To me, it seems like there's only one difference between DBO and SDE users. DBO controls everything while SDE only control the ArcSDE things. Beyond that, I don't see how SDE is any more organized than using DBO. In my diagrams above, the prefixes to the tables created with the DBO user are all different. I think you're telling me that that's what the SDE tables should look like also? I thought the point of the SDE user was to only have one user that's responsible for creating and maintaining feature classes/tables instead of 2+. You'd then have other logins loading actual data into those feature classes/tables, but not creating the actual fcs or tables.

So, let me use a real example and then maybe you can tell me how it would differ if we had used an SDE user instead of DBO user. Our current set up is using the DBO schema, our collection of tables looks like this in SQL Server:

Now, we have a login/user on that database called FocalAreasWriter. It's default schema is FocalAreasWriter. Logged in as that user, if I wanted to create a new table called "Quail", it would show up in that database as FocalAreasWriter.Quail. So, reasonably speaking, you could have FocalAreasWriter or any other number of users recreate the pictured tables above in their own schema name (e.g. Surveyor.CoveyObservation).

Now switch to an SDE schema set up, I guess all the special spatial tables (circled in red) would turn to 'sde' prefixes?

And the rest of the tables you'd want to create with different, more descriptive user/schemas, ending in something like these names:

Surveyor.CoveyObservation

Manager.FA_RA_CENTROIDS

Analysis.FAManagedDensity

Manager.FAUser

Manager.FOCALREFERENCEAREAS

Surveyor.FocalSpecies

I guess I'm not getting how that differs from dbo. You could make it just as organized in a dbo schema if you wanted to. How does the SDE schema help you organize? Or is the SDE schema really only about locking down your db further by:

1) Keeping the geodatabase admin (sde) from going outside their realm and messing with stuff in the overall database instance

2) Giving the geodatabase admin (sde) the same power as a database admin over granting permissions to various users working with spatial data

Appreciate your insight and patience.

Re: DBO vs SDE Schema Visual Diagram correct?

George_Thompson — Tue, 06 Apr 2021 20:01:28 GMT

If you did use the SDE vs. DBO schema as the GDB Admin, those repository tables would be owned by SDE. Now you do NOT see those tables in ArcGIS clients, only when logged in to SSMS. Which is where you usually do not manage the data tables

Yes, if you had additional schema owners, other than "FocalAreasWriter" they could create similar tables.

For your ending questions:

1. If you use the SDE schema, the SDE user can access all the tables that they have access to or owned by SDE. This is why we would recommend that you have all the data owned by other users.

2. I would not give the SDE user the same as a DBA, this would violate the least permissions theory. You leave the permissions to each table (spatial or non) to the table owner.

Let me know if I missed anything. I will say that many SQL Server users go with SDE schema, so that the SDE user is the Geodatabase admin, not the database admin. This helps isolate the users permissions across the entire DB.