DBO or SDE or what schema?

[TimHayes]

I am using ArcGIS 10.2.1 with SQL Server 2008 R2 OS Authentication.

To create a SQL Server Enterprise Geodatabase is it a requirement to use DBO or SDE Schema? can I create my own schema based on a user group name? for example, I have 3 users each with a different user name. I want all 3 to be db_owner. I can do this at the database level in SQL Server Management Studio. But, the ArcGIS documentation says the user name and schema must be the same name. If I am using DBO schema then my user name must be DBO. This is how I read it. Will it work if I create a user group named, for example: GIS_People; which contains the user names of 3 users; then create a schema named GIS_People; Or must I use only the DBO or SDE schema?

The ArcGIS requirement for the user name and schema name to be the same is really confusing the heck out of me. Other non-ArcGIS databases I am using in SQL Server, the user name and schema name can be different and there are no problems.

[WilliamCraft]

The requirement for ArcSDE is that the schema and the geodatabase administrator user account must be the same name.  You need to pick either DBO or SDE for in the case of SQL Server for the geodatabase administrator; those are your only two choices for that account.  This link provides some pros and cons to consider when deciding between them.  No matter which one you pick, the requirement is that the username and the schema name must match.  While you're right in that non-SDE databases don't have a requirement like this according to Microsoft, remember that we're talking about a special account here for ArcSDE not just an average user.  We're talking about the geodatabase administrator.  Once you set up your geodatabase, you can add whatever users and user groups that you want to the database and grant them whatever role you want.

[VinceAngelo]

Keep in mind that ArcSDE is supported across half a dozen RDBMSes. The ability
to have schemas which are different than the users which are different than the
logins makes it very difficult to look up metadata from the SDE tables in a timely
fashion.  Would the ability to randomly map user to schema be worth a 20%
performance penalty?  As it is, only data owners are required to have the same
schema as user, and best practice is to name the owners in a way that describes
the data (assessor.parcels, transportation.centerlines, etc.), so what real benefit
is there to "chartreuse.parcels"?

As to the DBO vs. SDE, the issue comes down to whether you want a wild-west
show or a well ordered database.  If you have a tiny department with one editor,
then there's little risk to using a DBO schema, but if you have a number of
editors and you don't want any user to be able to whack the geodatabase
through ignorance, then using a SDE owner grants you a finer level of control
to database administration.  You cannot use a custom schema for SDE table
ownership.

- V

[AbeCoughlin1]

New comment to old post..."Thaaanks ESRI!"..., and yes, that was meant to be sarcastic.

This 'wild-west' comparison would be good to put in the comparison article that's out there, because there's little suggestion of difference in impact, control or administration in that article.

Yes, I've gone the DBO approach, not wanting to have a Windows DBO user, and not wanting to always be the DBO while working in ArcGIS. I did as advised, create a separate data owner 'owner' with a 'owner' schema, so now there's a dbo, an owner and a user. It all works well... almost, but that's a topic for a new thread. I'll post it here if I whether I open one or solve it myself...

[feralcatcolonist_old]

I'd love to see all of this re-hashed again.

@VinceAngelo while the underlying technology hasn't changed too much I think that this topic has too many disparate sources to understand fully (especially all bunched around the beginning of the 10.x era.

I'd love to see a deep-dive blog where the Geodatabase team dives into the guts of this topic, canonically links to all the relevant source materials (some of which still live in the Desktop docs). I'd even be in for a 3-part series because the marketing team would love that.

[VinceAngelo]

Hmmm, well, you should probably ask the Geodatabase team to write that blog. I've been a PostgreSQL admin, nearly exclusively, for most of the past decade, so you wouldn't want me working on it anyway.

- V

[TimHayes]

I have set up a SQL Server Enterprise Geodatabase using OS Authentication and the DBO Schema.

I am the Geodatabase Administrator with the user name of "dbo" and the schema of "dbo".

I have 2 db_owners whose user names are their domain account names (mister_x and mister_y) who use the schema dbo.

It works fine, no problems. But I just want to understand more...

Based on what you are telling me here, if using the dbo schema the Geodatabase Administrator can only have one user name, and that is "dbo" since the user name must be the same as the schema name (dbo) for the administrator. Is this correct?

Do the same circumstances apply if I am using the SDE Schema? meaning must the Geodatabase Administrator have a user name of "sde" because the Schema is "sde". They must be the same as well?

Only 2 schemas can be used and these are dbo or sde, no others.

I am only talking about the Geodatabase Administrator, not other users. Adding other users I understand. I just want to get an idea on what user names the Geodatabase Administrator can use which seems to be only 2 user names depending on the schema you choose (dbo or sde).

[LeoDonahue · Accepted solution]

I just want to get an idea on what user names the Geodatabase  Administrator can use which seems to be only 2 user names depending on  the schema you choose (dbo or sde).        

When speaking to ArcSDE, there are two phrases which have always caused confusion.

Database Administrator
Geodatabase Administrator  (this is the one that gets everyone confused)

In SQL Server, a Database Administrator (dbo) owns everything and has control over other databases residing in that instance of SQL Server.

In SQL Server, a Geodatabase Administrator is talking about the user who owns/manages the ArcSDE related tables within a database.  That can be a dbo user (Windows account) or a sde user (SQL Server account)

As we have seen, depending which user owns/manages the ArcSDE related tables, it can make your life easy or hard.

In SQL Server, who owns/manages the ArcSDE related tables is either the "dbo" user (usually the same person who installed SQL Server) or a SQL Server account called sde.

If you choose to go with a dbo schema, the dbo user will own whatever data you load into the geodatabase.  All of your data will be prefixed with "dbo."  And possibly, any other user you grant DBO privileges can load data, and that data will be prefixed with that users schema name. 

You could end up with something like:
dbo.parcels and ldonahue.parcels  (whatever my windows login name would be)

Kind of sloppy if you ask me.  All of your ArcSDE related tables will also be prefixed with "dbo."

If you choose to go with a sde schema, the sde user will never and should not load data into the geodatabase.  All of your ArcSDE related tables will be prefixed with "sde."  But you are free to create other SQL Server accounts with the correct permissions to perform the role of data loading.  When data is loaded by a login user, it is owned by that login user's schema name.  As Vince pointed out, this gives you some nice organization to your data naming conventions if you created a SQL Server account called "Assessor".  Even though the "Assessor" is not you per se, it gives you metadata in the name of the layer.  Some people like that.

The other nice part about using sde schema is if you are not the Database Administrator of the SQL Server instance, you can still "administer" everything in your geodatabase that you need to using the sde user account or one of the data owner accounts.  And then the real Database Administrator can lock you out of everything else, that is if you are sharing an instance of SQL Server.

[MikeBly]

Thanks for this. This one of the most simple explanations that I have seen when the question of DBO vs SDE comes up.

[LeoDonahue]

And one last thing.

If you go with sde schema, and you should in my opinion, "you" as the administrator of the geodatabase in general should be the only one loading data into the geodatabase under the correct schema name depending on how you want to structure your data.   Schema names being something like your dept name for example.

If you think about this, how many non-gis developers out there get to load data into their company's database whenever they want?  I don't know many, if I can even name one.  That is usually a gatekeeper of somekind.  Be the gatekeeper.