topic Re: database username and current scheme dont match in ArcGIS Enterprise Questions

database username and current schema don't match

RandallClark1 — Mon, 09 Mar 2015 17:53:50 GMT

What exactly does this error mean and what do i have to do to stop getting it?

this is a sql server 2008,

Message was edited by: Asrujit SenGupta

Re: database username and current schme dont match

VinceAngelo — Mon, 09 Mar 2015 21:46:07 GMT

It means that your database table owner is not the same as the login user. You don't say what database you're using, but the non-Oracle databases permit "login foo" to be mapped to "schema bar" in database X, but since the geodatabase registry works off of login name, it can't tell who the rightful owner is (well, it could, but it would make every other operation so slow, the net result would be useless). So enterprise geodatabases require that the owner schema must be the same as the owner user, which must be the same as the login user.

You'll need to reload the data to clear this error.

- V

PS: Please edit your post ("Edit" menu choice on the upper right), so that "schme dont" is "schema don't", so others can use the exact error message to locate this question.

Re: database username and current scheme dont match

RandallClark1 — Tue, 10 Mar 2015 01:05:18 GMT

Hi vince,

Thanks for the response. the database is sql server 2008.

so how is that i can make a feature data set with no problem?

and how do i determine what the correct schema is?

Re: database username and current scheme dont match

VinceAngelo — Tue, 10 Mar 2015 01:53:55 GMT

There is no "correct" schema, just a matching one. For example, if I create a login "qcuser", then the username in all databases in which I intend to create geodata is "qcuser" and the schema will be "qcuser". The rules aren't as strict when users will only be using data created under other users, but I loathe the ability to randomly map users and schemas, and refuse to make my databases that chaotic.

Re: database username and current scheme dont match

George_Thompson — Tue, 10 Mar 2015 12:04:14 GMT

To add on to what Vince said, this is from Help Documentation for 10.2.x for database authentication

DB Authentication:

14. If the user requires a matching schema (for example, the user will own data in the geodatabase), type the schema name in the Default schema field. Remember, the user name and schema name must be the same if this user is going to own data.

Adding database-authenticated logins to a SQL Server database

Windows Authentication:

14. If you are adding an individual login rather than a group, and the user will own data in the geodatabase, type the schema name in the Default schema field. Remember, the user name and schema name must be the same.
For Windows groups, a matching schema will be created for any group member the first time the user creates data in the geodatabase. The schema name will be the same as the login name, not the name of the group. This is done automatically; you do not have to create the schema ahead of time.

Adding Windows-authenticated logins or groups to a SQL Server database

Hope this helps clear up any confusion.

-George

Geodatabase Managing Data

Re: database username and current schema don't match

DrewDowling — Tue, 14 Aug 2018 16:51:53 GMT

As a further note I also found out that the schema name and user name must also match case. I had user "UserA" who was mapped to a schema "usera" and I got this error. Once I dropped the schema and recreated it as "UserA" the error went away.

Re: database username and current schema don't match

VinceAngelo — Tue, 14 Aug 2018 16:55:47 GMT

On some (most?) databases, uppercase schema names are not permitted at all.

- V

Re: database username and current schema don't match

DrewDowling — Mon, 20 Aug 2018 14:23:32 GMT

SQL server seems to allow mixed by default, at least in my experience. I'm guessing since the default collation is case insensitive but don't know for sure.

Re: database username and current schema don't match

MatthewRyan2 — Wed, 25 Sep 2019 04:24:13 GMT

I strongly disagree with your premise on "I loathe the ability to randomly map users and schema".

What we're actually doing is logically managing and categorising data inside our databases. As that is the number 1 purpose of a database I find it nonsense to say "Anyone creating tables or objects in a database must create them inside their own user schema".

The choice of schema should be given to the user who is creating the object in order to avoid creating a chaotic database where no one knows where data is or how to find it.

Consequently, I'd love to see a feature letting us choose which schema to create our objects in. As noted throughout this thread the databases operate in this model so I don't understand why it's so hard to adopt their practice and include a text field "schema"

Re: database username and current schema don't match

KatherineBarton — Fri, 27 Sep 2019 17:56:37 GMT

Yes! Absolutely agree. The fact that we cannot choose the schema we are using when attempting to load data is absurd. This is made even more difficult when attempting to render a table that was not created from ArcMap, as the 'Shape Integrity' error now appears regardless of actual shape integrity.

Re: database username and current schema don't match

AndresCastillo — Mon, 07 Mar 2022 23:15:09 GMT

Found some doc on the above:

https://pro.arcgis.com/en/pro-app/latest/help/data/databases/dbms-support.htm

Although PostgreSQL allows user accounts to write data to schemas of any name, ArcGIS requires that user accounts that create tables, views, or other objects in the database have identical user names and schema names. For example, you cannot connect from ArcGIS with the user name vlad and write to the public schema.