topic Re: Feature Names in SDE Geodatabase in Data Management Questions

Feature Names in SDE Geodatabase

BrendanDwyer — Fri, 04 Jan 2013 17:09:18 GMT

I've installed SDE 10.0 on SqlServer 2008 on a 2008 R2 server. We are using the dbo schema and windows authentication. There is an active directory group ("ups") that all our users belong to. We added the ups group as a user to the database and gave it datawriter and datareader permissions.

Users can log in and see the feature classes, but when they create a feature class, it adds the users name to the feature class name. For example our database is named upssde, the user is John Smith, the feature class gets names "upssde.'John.Smith'.roads"

According to this page* SDE is creating a schema with the name of the user.

We can't have the user name as part of the feature class name, and, obviously, this isn't how SDE is supposed to work. I'm pretty sure I don't have the roles/permissions/groups set up right. I'm not well versed in SQL Server. Is there a way to set it up so that the user names don't show up in the feature class name?

Any help would be appreciated.

*For Windows groups, ArcGIS will create a matching schema for any group member the first time the user creates data in the geodatabase. The schema name will be the same as the login name, not the name of the group. This is done automatically; you do not have to create the schema ahead of time.

Re: Feature Names in SDE Geodatabase

ShannonShields — Fri, 04 Jan 2013 17:50:57 GMT

That documentation link isn't entirely accurate. It is SQL Server, not ArcGIS, that is automatically creating a user and schema when a Windows Group member creates a feature class or table.

User names will always "show up" in the name of an object displayed in ArcGIS because we show you the fully qualified name of that object - database.schema.table, e.g. upssde."John.Smith".roads. It is how you distinguish between upssde."John.Smith".roads and upssde."Dave.Jones".roads. The feature class itself is still just "roads", but as there may be many "roads" tables on different schemas, created by different users referencing it by its fully qualified name is now you make sure you are accessing the correct table.

Thank you for pointing out the documentation link - I'll see to it that it gets updated correctly.

-Shannon

Re: Feature Names in SDE Geodatabase

BrendanDwyer — Fri, 04 Jan 2013 18:34:13 GMT

Shannon,
Thanks for the reply.

So SDE/ArcCatalog is supposed to display the feature class as "upssde.'John.Smith'.roads"? I thought I was doing something wrong when I set it up. Will it always assign the Active Directory user's name to the schema/feature class? I thought if I assigned editors to a role or a group, the feature class would take the role or groups name.

A few more questions: What happens when multiple users edit a file? Will the file always have the name of the user that created it? And if we export the feature class, feature dataset or database, will it display the users name as part of the feature name?

I really appreciate the quick reply and I apologize for the barrage of questions.

-Brendan

Re: Feature Names in SDE Geodatabase

VinceAngelo — Fri, 04 Jan 2013 19:16:44 GMT

Keep in mind that they're tables, not files. Tables have to have an owner. The group
grants access, but the user owns the tables. Best practice is to create one or more users
to own the tables, then grant access to users (or groups) through roles.

Once data is exported, it usually doesn't really matter whether the previous owner is
part of the export (whether it does depends on the method of export -- there's a *lot*
of possible paths), since the user used to import into the next database will become
the new owner.

- V

Re: Feature Names in SDE Geodatabase

BrendanDwyer — Mon, 07 Jan 2013 14:10:47 GMT

Vince,
Thanks for following up. I just want to clarify something in your post. You said that the best practice is to create a user to own the tables. If we are using windows authentication, the user would have to be a user in Active Directory, correct?

Re: Feature Names in SDE Geodatabase

VinceAngelo — Mon, 07 Jan 2013 14:30:04 GMT

Local security guidelines would of course take precedence, but the owner account would
generally be a "headless user", and therefore better map to a DBMS login (named for
the theme, department, or data source, e.g., "Basemap", Transportation", "USGS").
The password would be closely held, to make sure that it was only used by the data
librarian for changes to content and database design that have been approved for
use within the system.

- V

Re: Feature Names in SDE Geodatabase

BrendanDwyer — Mon, 07 Jan 2013 16:19:36 GMT

OK. Thanks for the clarification.

Re: Feature Names in SDE Geodatabase

ShannonShields — Mon, 07 Jan 2013 21:12:10 GMT

Brendan,

>So SDE/ArcCatalog is supposed to display the feature class as "upssde.'John.Smith'.roads"?
Yes, this is correct. We'll always display the fully-qualified name. This may be different depending on your data's source. In the case of SQL Server, it is database.schema.table

>Will it always assign the Active Directory user's name to the schema/feature class?
The basic model is that the user that creates the feature class "owns" it. SQL Server operates a little bit differently in that several releases ago (SQL Server 2005) they removed the concept of "ownership" as it applies to most database objects. Instead, within a database, there can be one or more namespaces called schemas. When data is created, it is associated with a particular schema. So, you could create a schema called 'Payroll' that housed all your payroll data, and multiple users could have permission to create data on the Payroll schema. John.Smith could create a table on Payroll by specifying the schema name in the create table statement - CREATE TABLE Payroll.Employee. Even though he created the data, he doesn't actually own it.

However.... and this is a big however, ArcGIS doesn't support schemas in SQL Server this way. We are working on addressing this in a future release, but right now we are still tied into this concept of ownership - the user who creates the data owns the data. There are several operations that restrict functionality based on ownership. For example, when using ArcGIS only the owner of a feature class can add or remove columns from it, or grant permission to other users to see it. Despite the fact that SQL Server may allow other users with elevated permission to perform these tasks, ArcGIS will restrict them. So, if your users are going to create and manage data in a SQL Server database using ArcGIS, any user who will create tables or views must have a default schema that has the same name as their user name. Users can only create data on their default schema, and that schema's name must match the user's.

> I thought if I assigned editors to a role or a group, the feature class would take the role or groups name.
No. Once an object is created on a particular schema, it stays on that schema. If John.Smith creates a feature class named roads, it will be referenced as "John.Smith".roads, regardless of John.Smith's role or group membership.

>What happens when multiple users edit a file? Will the file always have the name of the user that created it? And if we export the feature class, feature dataset or database, will it display the users name as part of the feature name?
Having the ability to edit a table is separate from being able to create one. Users can be granted permission to perform edit operations on a table, but that has no impact on the schema that the table resides on. If you export the feature class, the user name will not be part of the export. If the table is exported to another SQL Server database, the user that performs the export operation will be the new "owner" of the table. So if Dave.Jones exports John.Smith's roads feature class into a new database, that table will become "Dave.Jones".roads.

-Shannon

Re: Feature Names in SDE Geodatabase

BrendanDwyer — Wed, 09 Jan 2013 17:57:32 GMT

Shannon,
Thanks for the detailed post. I appreciate it.

This database is going on a system where we don't want the users AD name as part of the layer name. Is there any way to configure SQL Server or SDE using windows authentication where we can dictate what is displayed as the schema name? For example, instead of uspsde.'John.Smith'.roads, we can configure it so that it displays (for example) upssde.Florida.roads?

Thanks,

Brendan

Re: Feature Names in SDE Geodatabase

LeoDonahue — Wed, 09 Jan 2013 23:56:23 GMT

Vince has already mentioned that you can do that by either mapping to a headless dbms account, or make one or two people data owners. Unless you have active directory logins named Florida, you might consider making a dbms account for loading/owning certain data.The person who exports a table from database 1 to database 2 needs to have data owner permissions.

Re: Feature Names in SDE Geodatabase

LeoDonahue — Wed, 09 Jan 2013 23:58:52 GMT

On database 2. Sorry for the double post, my phone doesn't give me the option to edit the last post once submitted.

Re: Feature Names in SDE Geodatabase

BrendanDwyer — Thu, 10 Jan 2013 10:36:49 GMT

Vince has already mentioned that you can do that by either mapping to a headless dbms account, or make one or two people data owners. Unless you have active directory logins named Florida, you might consider making a dbms account for loading/owning certain data.The person who exports a table from database 1 to database 2 needs to have data owner permissions.

IT won't let us use DB accounts because the passwords are sent across the network unencrypted. And they won't let us create a headless AD account. I want to know if there is any way to use OS authentication and not have the data owners AD name show up in the feature class name.

I understand why you would want to know who created the dataset, but from an organizational point of view, I'd rather have something descriptive about the data as part of the displayed name (for example upssde.Florida.roads, instead of upssde.'John.Smith'.roads). If I want to find out the owner name, that's what metadata is for.

Re: Feature Names in SDE Geodatabase

LeoDonahue — Thu, 10 Jan 2013 12:42:11 GMT

I want to know if there is any way to use OS authentication and not have the data owners AD name show up in the feature class name.

No, as Shannon has pointed out.

>Will it always assign the Active Directory user's name to the schema/feature class?
The basic model is that the user that creates the feature class "owns" it. SQL Server operates a little bit differently in that several releases ago (SQL Server 2005) they removed the concept of "ownership" as it applies to most database objects. Instead, within a database, there can be one or more namespaces called schemas. When data is created, it is associated with a particular schema. So, you could create a schema called 'Payroll' that housed all your payroll data, and multiple users could have permission to create data on the Payroll schema. John.Smith could create a table on Payroll by specifying the schema name in the create table statement - CREATE TABLE Payroll.Employee. Even though he created the data, he doesn't actually own it.

However.... and this is a big however, ArcGIS doesn't support schemas in SQL Server this way. We are working on addressing this in a future release, but right now we are still tied into this concept of ownership - the user who creates the data owns the data.

Re: Feature Names in SDE Geodatabase

ShannonShields — Thu, 10 Jan 2013 20:40:59 GMT

For example, instead of uspsde.'John.Smith'.roads, we can configure it so that it displays (for example) upssde.Florida.roads?

Brendan,

you could add the John.Smith AD login to SQL Server, and make a user named 'Florida' in the upssde database that is associated with the John.Smith login.

The SQL would look something like this:

CREATE LOGIN [DOMAIN\John.Smith] FROM WINDOWS WITH DEFAULT_DATABASE=[upssde]
GO
USE [upssde]
GO
CREATE USER [Florida] FOR LOGIN [DOMAIN\John.Smith]
GO
ALTER USER [Florida] WITH DEFAULT_SCHEMA=[Florida]
GO
CREATE SCHEMA [Florida] AUTHORIZATION [Florida]

This would mean that everytime John Smith connected to the upssde database he would be a user named 'Florida'. You could not do this if he was added by way of an AD group, only if he was added individually to the SQL Server instance.

Anytime you wanted data created on the 'Florida' schema, John Smith would have to be the person logging in to perform this task.

Not really convenient, but it can be done if you are using individual Windows logins, but not groups.

-Shannon

Re: Feature Names in SDE Geodatabase

ThomasColson — Wed, 17 Jun 2015 17:47:27 GMT

From Having Trouble Getting Data Creators Group to Function Properly the statement "If using a DBO schema geodatabase, only users, or groups which assign user's sysadmin rights within the SQL Server instance will be able to create data." is confusing. We desire that all objects are in the dbo schema, and DO Not want fred.roads and tim.roads. In addition, only DBO's (a very few) are authorized to create objects (feature classes, etc..) and edit them (add columns). Is there ESRI documentation that supports that in order to effect that, the dbo must also be sysadmin? This conflicts with IT security requirements, which strictly limit the sysadmin role.