ArcGIS Enterprise MS SQL Server Database Users vs DBO

Question

Hello,

Initially, I was creating tables in the Enterprise GDB (EGDB, MS SQL) as the Database Owner (dbo schema), and everything worked fine, and still does as DBO.  I am able to read/write content in ArcGIS Pro and Python via ArcGIS for Python API or SQLAlchemy.  Along the way, I created a user so we didn't share credentials.  This user can operate just fine in ArcGIS pro, but any attempts to read/write/access his own tables (or privileged tables) using Python/SQLAlchemy fail with login error "[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user 'user'. (18456)".

The user can, however, execute the following with his credentials...

cursor.execute("select name from master.sys.databases")

..and get a return of those high level tables.  Login fails trying to read EGDB tables (feature classes).

Any thoughts would be greatly appreciated.

Tyler

Enterprise 11.2

George_Thompson · Answer

Thanks for that information and context. Here is what I am hearing, for clarity:

Works: when using Pro client and the user 'test', no issues.

Error: when using Python/SQLAlchemy script and the 'test' user credentials.

Always works with DBO (database admin) credentials.

I am not sure what would be causing the issue if it works in Pro, at the DB level.

You may want to work with technical support on this. Seems like a Python side issue?

TylerT · Answer

Hi @George_Thompson,
The 'user' is trying to access tables created both by 'user' and DBO user.  I'm getting this error:

Again, no issues in ArcGIS Pro for user 'test'....and no issues with ArcGIS Pro or Python/SQLAlchemy with the DBO user credentials.  It's just when I use Python/SQLAlchemy and user 'test' credentials do I have an issue.  Somehow user 'test' is getting blocked at db level.

I did create a new user as suggest, and the same error behavior occurred.

Thanks for any further advice.

Tyler
Enterprise 11.2

George_Thompson · Answer

Is the "user" account trying to access the tables created by the DBO user?

Have you granted permissions to the "user"?

Can you create another user, like "gis", and see if that account works?

Here are the permissions needed for each type of user: https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/privileges-sqlserver.htm