Can the SQL Server schema be specified when creating a new feature class?

[AndrewGilbert]

I'm connecting to a SQL Server database using "Operating system authentication" (in this case Active Directory pass-through). To keep data organized, I would like to create feature classes in different schemas within that database. I can create the schemas ahead of time in SSMS (SQL Server Management Studio).

However, when I go to create a feature class through ArcGIS Pro or ArcMap, I don't see an option to select a schema. Can the schema be specified when creating a new feature class?

Operating system authentication is a hard requirement, but I'm open to workarounds that involve schema owners.

[BillFox · Accepted solution]

You could use database authentication to create ArcSde connection files for each "owner" you want to be as you create the items owned by them.

Then manage security using AD security groups mapped to SQL instance logins mapped to SQL database roles applied to the items via esri ArcMap/Pro.

[DavidHoy · Accepted solution]

Andrew, to expand on Bill's answer.

You cannot create new classes in a schema that is not the logged in user's own. I.e. User named mydomain\user1 cannot create feature class (table) in the "dataowner" schema.

So, if you want to use specific schema (and this is a good data organisation practice), as Bill says, you need to use Database Authentication, create Logins and add them to the Database with sufficient privileges to create content (db_owner would be OTT, these database users only need 
CREATE TABLE
CREATE PROCEDURE
CREATE VIEW

Then, create database connection files for each of these Users, use these connections when creating new classes (or updating their structure/indexing). The new tables will automatically be created within the schema that has the same name as the login.

Once created, you can assign required privileges to the new classes for Roles in the database (that may be associated with AD Groups) That way, your OS login can be allowed to see/edit the datasets that are appropriate for them. The use of schema in this way helps to groups data that has similar source or purpose.

[AndrewGilbert]

Thank you Bill and David.

@DavidHoy,

If a user creates a new feature class when logged in with OS authentication, the feature class is created in the default schema for that database every time, correct? I was hoping maybe a database configuration keyword (last page when creating a feature class) could help drive the schema used.

Also what is "OTT"?

[DavidHoy · Accepted solution]

Andrew, what you say may be true if you are using SQL to create new tables, but ArcGIS enforces a rule that only a schema with the same name as the connected user may be used. 

See https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-sql-server/add-users-sqlserver.htm

 "Database users who will create tables and feature classes must have privileges necessary to create these objects in the database, and they must have a schema in which they can create them. When using ArcGIS, that schema must have the same name as the database user."

If you try to add a new feature class and teh default schema name is not the user's name, you will get an error message something like

[DavidHoy]

OTT = "Over the top"

see https://www.urbandictionary.com/define.php?term=OTT

🙂

[AndrewGilbert]

Perfect, thanks @DavidHoy. That clears things up.