DBO vs SDE Schema Visual Diagram correct?
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:
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.
