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; } } }

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.

Encuentra más publicaciones etiquetadas con

Enterprise geodatabase

Accepted answers

BillFox

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

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.

DavidHoy

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

All comments

BillFox

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

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.

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

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

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

AndrewGilbert

Perfect, thanks @DavidHoy. That clears things up.

DavidHoy

OTT = "Over the top"

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

🙂

TylerT

@BillFox,
Are you saying that if I want a schema named 'COMMON' (i.e. 'COMMON.TBL1), then I need to create a Database user named 'COMMON', and then anybody who needs to write to COMMON schema has to share the db credentials (user name and password) or made sde connection? This all given there is a COMMON schema in the db (which I have created). Let me know. Thx.

George_Thompson

Yes, you are correct @TylerT . You would create a user (with matching schema) and load data as that user. Using the GP tool will make sure that user has the correct permissions, etc.

You "could" share the connection file for the COMMON user if other needs to load data in that schema.

MuneerMajid

I have a scenario where a user (SQL login) that owns a schema, sees the tables listed contents tab that are owned by another User. All the users are created as logins on the master, then added as a user into the master. So they have the public role by default.

Then a User is created in the database using the same login, and it is given authorization with default_schema on a schema with the same name. Yet all the users are able to list tables not owned by them in the contents tab. Is there a way to ensure that each schema owner login only sees the tables owned by them ?

DavidHoy

Hi,
By default, only the data owner can "see" the items in their schema - and that is reflected in the Catalog pane of ArcGIS Pro.
but, this could happen if your users (or a role/group of which they are a member) had been granted access by the schema owner, or have been granted db_datareader or db_datawriter system role.

Únete a la Conversación

Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.

Enlaces Rápidos

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