Feature Names in SDE Geodatabase

[BrendanDwyer]

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.

[LeoDonahue]

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

[BrendanDwyer]

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.

[LeoDonahue]

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.

[ShannonShields]

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

[ThomasColson]

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.