Question about AD Groups in SQL & permissions

[TSmith]

Not sure if this is the right place to ask this, was curious if anyone could point me in the right direction. 

We created a new AD group, added it to our SQL server. The users in this group, when they connect through SSMS, they are able to create tables with "dbo" as the data owner. In ArcGIS pro... they can connect to the DB as OS users, they can see the DB, and they can create tables. However, the tables created have their full AD login as data owner, and not dbo. (i.e., USER.TABLE as opposed to DBO.TABLE) 

The kicker is, I cannot replicate this issue myself with my own DB connections. The user also gets errors when trying to publish to ArcGIS Enterprise, saying the dataset is not registered with the server (it is) and I think this is because somehow, Pro is mapping their SDE connection as their actual AD user, and not using the AD group to manage the schema (set to dbo by default)

Any ideas? 

User is mapped to db, default schema = dbo

[ChrisUnderwood]

Hello @TSmith , Which "Authentication Type" have you and your users selected in your Database Connection ?

[TSmith]

OS authentication. I am a member of an Enterprise DB Admins AD group and have no problems creating data in DBO schema, we setup an AD group for a specific project to provide access to one DB, and for some reason the users in that group have been experiencing issues with table/FC creation being in DBO schema. 

Being honest here, I am not a fan of AD groups at all but this is the direction our IT dept prefers to go. The funny thing is, on the SQL side, it works fine. The users in that AD group can ONLY access the DB that group is mapped to, and none of the other project DB's, it's the connection from ArcGIS Pro that is behaving differently than expected. 

Normally, I would just create a database user and give the credentials to the people who need it, but that isn't the most secure method and had a lot of pushback internally.

[ChrisUnderwood]

So for your Publishing part of the question, you need to remember that the "user" who will access the Geodatabase data through the service is not the user who publishes the service from Pro, it will be the ArcGIS Server Account, ie the "Log on As" user that runs the ArcGIS Server service

https://doc.esri.com/en/arcgis-enterprise/latest/plan/the-arcgis-server-account.html?pivots=os-windo...

[TSmith]

Yeah, the weird thing about that is- this user registered the DB with data store (created the item on portal) and validated without issue. But when they try to publish a layer, they get this: 

this is also an issue I cant replicate myself, i'm not entirely sure why. 

[George_Thompson]

This is due to the requirement that the username / schema name match in ArcGIS.

"Users who own data must also have schemas in the database that have the same name as the username." https://doc.esri.com/en/arcgis-pro/latest/help/data/geodatabases/manage-sql-server/user-accounts-gro...

Also review this doc: https://doc.esri.com/en/arcgis-pro/latest/help/data/geodatabases/manage-sql-server/add-users-sqlserv...

I would not usually recommend that an AD group be granted db_owner as they can make changes to the database that could cause negative impacts. That is like "admin" privileges to everything.