database username and current schema don't match

25134
10
03-09-2015 10:53 AM
RandallClark1
New Contributor III

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

0 Kudos
10 Replies
VinceAngelo
Esri Esteemed Contributor

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.

RandallClark1
New Contributor III

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?

0 Kudos
VinceAngelo
Esri Esteemed Contributor

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.

George_Thompson
Esri Frequent Contributor

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

GeodatabaseManaging Data

--- George T.
DrewDowling
Occasional Contributor III

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.

VinceAngelo
Esri Esteemed Contributor

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

- V

DrewDowling
Occasional Contributor III

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.

0 Kudos
MatthewRyan2
New Contributor III

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"

KatherineBarton
New Contributor

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.