nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

One more: database username and current schema don't match

Ravichandran_M_Kaushika

Good afternoon.

I am trying to debug a SDE sql server to file geodatabase missing records during export from both Arc Catalog and ArcGIS Pro - that is a critical task in our office.

ESRI rep suggested to me 'move' that file gdb layer to SQL Server to compare the missing records to determine the next steps. I thought it would be a piece of cake. It turned out to be a very challenging task.

When I tried an import into SDE from a file geodb, At first I got ERROR: 000210 - Cannot create output db connections\mySDEConn.sde\myTargetLyr

failed to execute (FC to FC).

I am not a SQL dba - let alone a SQL SDE dba.

So I located the security panel on SQL Server Management Studio and made my accounts (windowsDomain\acct1 and database user acct2) as gods for that particular database I wanted to import the records. so I did an import into SDE from these 2 SDE connections and I got this error:

Failed to Create Feature Class:

Database user name and current schema do not match.

[mySpatialDB."WINDOWSDOMAIN\ACCT1".testme] and [mySpatialDB."ACCT2".testme]

https://community.esri.com/t5/arcgis-enterprise-questions/database-username-and-current-schema-don-t-match/m-p/541404

A couple of users at the bottom of the posting had expressed their displeasure on the thought that user name had to be same as the schema...🤔. I lost 6 hours trying to import 1 feature from file geodb to sql server sde.

Luckily, this user name and schema mangling did not happen on the sql server side. We have a low level application user with privileges to run a stored procedure that dynamically names and creates tables (layer with geometry) . Luckily, it created it as dbo.

I am lost right now - trying to fix my original problem of identifying the reasons for missing records when going from SQL Server SDE to file geodatabase.

sorry for off loading my troubles. any suggestion

thanks and regards

ravi.

Vind meer berichten getagd met

Accepted answers

John_Spence

Good morning.

First off, the if you want a working SDE enabled database, you need to have the user and the schema names to be the same. It is possible to do this with domain controlled accounts, but it is far easier to just use a SQL account instead or if you are the DBO to drop the dataset into the DBO schema. DBO is the only exception to above named rule of schema matching the user scenario.

If you want to have AD accounts though, here is the script I use for my one SDE database where I do this in.

USE [master]
GO
CREATE LOGIN [DOMAIN\UserName] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

USE [YourDatabaseName]
GO
CREATE SCHEMA [DOMAIN\UserName]
GO
CREATE USER [DOMAIN\UserName] FOR LOGIN [DOMAIN\UserName]
ALTER ROLE [db_datareader] ADD MEMBER [DOMAIN\UserName]
ALTER ROLE [db_datawriter] ADD MEMBER [DOMAIN\UserName]
ALTER USER [DOMAIN\UserName] WITH DEFAULT_SCHEMA=[DOMAIN\UserName]
ALTER AUTHORIZATION ON SCHEMA::[DOMAIN\UserName] TO [DOMAIN\UserName]
GO
GRANT CONNECT TO [DOMAIN\UserName]
GRANT CREATE FUNCTION TO [DOMAIN\UserName]
GRANT CREATE PROCEDURE TO [DOMAIN\UserName]
GRANT CREATE TABLE TO [DOMAIN\UserName]
GRANT CREATE VIEW TO [DOMAIN\UserName]
GRANT INSERT TO [DOMAIN\UserName]
GRANT UPDATE TO [DOMAIN\UserName]
GRANT VIEW DATABASE STATE TO [DOMAIN\UserName]
GRANT VIEW DEFINITION TO [DOMAIN\UserName]

Obviously, you'll need to put your info into the applicable areas, but this will generically get you what you are looking for.

All comments

MichaelGinzburg

Hello,

mySpatialDB."WINDOWSDOMAIN\ACCT1".testme

[mySpatialDB."ACCT2".testme]

The SQL table fully qualified name is database.schema.table.

It seems you use your logon as schema name.

Did you created your own schema? Otherwise it's either dbo (default schema) or sde.

Try to use mySpatialDB.dbo.testme or mySpatialDB.sde.testme in your mySDEConn.sde.

It will be also useful if you will mention your SQL version, SDE version and ArcGIS version.

George_Thompson

As for the logins and data owners (schemas) here is the doc: https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/add-users-sqlserver.htm#ESRI_SECTION1_1EF70F32F80041FB9666781BD3D78754

For the comparing part you may want to look at this tool Feature Compare (Data Management)

Is the data version in the Enterprise Geodatabase (SQL Server) that is being exported to the file Geodatabase?

Another good check would be to run the Check Geometry (Data Management) on the feature class in SQL Server first.

John_Spence

If you want to have AD accounts though, here is the script I use for my one SDE database where I do this in.

USE [master]
GO
CREATE LOGIN [DOMAIN\UserName] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

USE [YourDatabaseName]
GO
CREATE SCHEMA [DOMAIN\UserName]
GO
CREATE USER [DOMAIN\UserName] FOR LOGIN [DOMAIN\UserName]
ALTER ROLE [db_datareader] ADD MEMBER [DOMAIN\UserName]
ALTER ROLE [db_datawriter] ADD MEMBER [DOMAIN\UserName]
ALTER USER [DOMAIN\UserName] WITH DEFAULT_SCHEMA=[DOMAIN\UserName]
ALTER AUTHORIZATION ON SCHEMA::[DOMAIN\UserName] TO [DOMAIN\UserName]
GO
GRANT CONNECT TO [DOMAIN\UserName]
GRANT CREATE FUNCTION TO [DOMAIN\UserName]
GRANT CREATE PROCEDURE TO [DOMAIN\UserName]
GRANT CREATE TABLE TO [DOMAIN\UserName]
GRANT CREATE VIEW TO [DOMAIN\UserName]
GRANT INSERT TO [DOMAIN\UserName]
GRANT UPDATE TO [DOMAIN\UserName]
GRANT VIEW DATABASE STATE TO [DOMAIN\UserName]
GRANT VIEW DEFINITION TO [DOMAIN\UserName]

Obviously, you'll need to put your info into the applicable areas, but this will generically get you what you are looking for.

MargaretBaker

I would never recommend anyone load feature class to sde schema. This should only contain the system tables used to store sde & geodatabase specific info.

In this case, as the user has made themselves a super user (I guess meaning a member of sysadmin role) then creating in dbo schema will work out ok

MargaretBaker

also,

one possible reason you may be having mismatch between what you see in the SQL Server database and the records you are exporting to fgdb it may be that (maybe) you are using SQL to query the table but exporting using GP tool or right click options in Pro or ArcMap.

If the class happens to be versioned, then the base table will not necessarily contain all the records you see in the desktop client.
To get an accurate view of the records in a versioned class with SQL you would need to query the versioned view - for featureclass called fc01, the view to query would by default be automatically created as fc01_vw - see https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/what-is-a-versioned-view.htm

DavidHoy

but, in general, I believe it is rarely a good idea to use individual's schema to hold data that will need to be shared (or used later by some other user).

If not using a dbo account, It is generally a far better practise to use specific data owner/schema, e.g gis_owner and connect as that login when adding new datasets. You may consider a few individual data owners for different themes or external sources (e.g. land. assets, hydro etc). This also also provides a naming convention that should assist in finding your datasets (remembering the fully qualified name is database.schema.datasetname)
Once a class is created, then grant access to appropriate "gisreaders" and/or "giseditors" database roles.

That way, everyone knows that when connected using Operating System authentication their individual AD login, if in the appropriate role, will only see the feature classes they have been authorised to see/edit.

John_Spence

Agreed...for AD tied schemas, not a great idea and definitely NOT a best practice from a spatial database perspective nor Windows security environment.

The schema housing SDE and its components should never be used to store you data sets too.

Ravichandran_M_Kaushika

I got around the problem by creating a schema that matched the app user id on SQL Server database. I was not a great fan of that idea. App user registered on the database server was modified to have the default schema to match the newly created schema on the spatial database.

Once this was setup, I could transfer records from FGDB to SDE.

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.