Creating data in an ArcSDE geodatabase under a different schema

[OliverNM]

In the current configuration of the sql server, all users primarily had SS logins.  There was one account everyone used (gisadmin) which is a sysadmin account, to create data in the database.  The owner of all the data is dbo, and all data follows the following convention:

database.dbo.featureclass

and all SPs for moving data are written referencing dbo, where needed.

We would like to move to server logins using windows authentication, to that end, all users have been added, i have put them in all databases with the db_owner role.

According to ESRI:

'In Microsoft SQL Server, database administrators add logins to the SQL Server instance, and these logins are mapped to users in individual databases on the SQL Server instance. 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.'

Now, with some work, i managed to allow a user to create data, but  the schema is not dbo, but their username (e.g. database.username.featureclass.

How do I get Sql server login Domain\username to be able to create data in the geodatabase in the format that currently exists (i.e.  database.dbo.featureclass) ?

I hope I explained this well.

[Asrujit_SenGupta]

A comparison of geodatabase owners in SQL Server—Help | ArcGIS Desktop 

The dbo user

The dbo user and its default schema exist in all databases automatically. Logins can be dbo in a database in one of two ways:

• By creating or being made owner of a specific database
• By being a member of the sysadmin fixed-server role

In your scenario, where you need multiple Windows Authenticated logins to connect and create data as DBO, you will have to grant the 'sysadmin' server role to them.

[OliverNM]

I understand what you are saying, but we are trying to tighten up the security on our sql server.  Making everyone a sysadmin would definitely defeat what we are trying to do.

Making everyone db_owner in SS would not provide the necessary level to create data in the dbo schema ?  Is that not what your first point is eluding to ?

[Asrujit_SenGupta]

No, db_owner and the owner of the database are NOT the same.

You can have only one login as the owner of a database. That is why, I had mentioned 

In your scenario, where you need multiple Windows Authenticated logins to connect and create data as DBO, you will have to grant the 'sysadmin' server role to them.

What is the difference between "db_owner" and "the user that owns the database" in SQL Server 2000? ... 

MS SQL: dbo versus db_owner – GOVARDHAN GUNNALA 

[OliverNM]

Very good information Asrujit, thank you.

I don't mind data being created in our edit db with the users name, but i don't want to have to rewrite all the stored procedures or create ones that reference a specific user.  All our SPs take data from edit db and move it to various publication databases.  Do you have any thoughts on a workaround so published data will be 'owned' by dbo ?

Would functions like ALTER_AUTHORIZATION or ALTER_SCHEMA be applicable here ?

What about a SP that would run on the edit db and if the owner <> to dbo, change it to dbo ?

[Asrujit_SenGupta]

No, to change the Data owner in an Enterprise geodatabase, you will have to reload the data connected as required user using ArcGIS Desktop. Trying to change it from the database side risks corrupting the data, as entries are made to different system tables. 

Why not create and use a single SQL Server Authenticated login as the login for all Data Loading purpose. Make it the owner of the concerned databases and map it to dbo.

Or use a secure 'sysadmin login' to load the data into the Publication databases from the Edit databases.

[OliverNM]

For this option:

'Why not create and use a single SQL Server Authenticated login as the login for all Data Loading purpose. Make it the owner of the concerned databases and map it to dbo.'

The SS login would be owner of the database but would NOT have to be sysadmin ?

How would this scenario work ?  Are you suggesting copy/paste ?  There are too many data sets to do that.

'use a secure 'sysadmin login' to load the data into the Publication databases from the Edit databases.'

[Asrujit_SenGupta · Accepted solution]

The SS login would be owner of the database but would NOT have to be sysadmin ?

Yes, correct..

How would this scenario work ?  Are you suggesting copy/paste ?  There are too many data sets to do that.

'use a secure 'sysadmin login' to load the data into the Publication databases from the Edit databases.'

I didn't mean manually. From your post above, it seems you have already setup an automatic way to move data from the Edit DBs to the Publisher DBs...so just use a secure sysadmin login to connect to the Publisher DBs and execute the workflow.

[OliverNM]

Asrujit, I am going to try this, thank you !

This was the response I got from ESRI as well, it reiterates some of the points you were making.

...database logins can create data in dbo schema if they have sys admin fixed server role. Users can create data in a geodatabase if they have the four necessary permissions: create procedure, create function , create table and create view, and the data will be created in the schema of the user(Username=Schema name). 

Also, only the owner of the data can manage the data and provide privileges over it and there is no way to change the owner of a particular data unless he does a copy/paste of the data by making a connection to a different user.

Given below is a web-links for your reference :

Privileges for geodatabases in SQL Server-
http://desktop.arcgis.com/en/arcmap/10.4/manage-data/gdbs-in-sql-server/privileges-sqlserver.htm

Again, thanks.