Database User name and Schema name must be the same?

[TimHayes]

Using ArcGIS 10.2.1 and SQL Server 2008 R2; Using the dbo (NOT sde) schema. Have enabled a SQL Server Enterprise Geodatabase.

Must the database user name and schema name be the same? for example in SQL Server Management Studio:

Database User Name is "X"

Database Schema Name is "dbo"


Is this ok?

OR

must it be like this:

Database User Name is "dbo"

Database Schema Name is "dbo"

Is there a way to get the SQL Server Enterprise Geodatabase to function with a user name and schema name that are different?

[AsrujitSengupta]

Tim,

As mentioned in the below link, "Remember, the user name and schema name must be the same if this user is going to own data." -- Point #14

Adding database-authenticated logins to a SQL Server database:
http://resources.arcgis.com/en/help/main/10.2/index.html#//002q0000002m000000

Instead of trying to match the username with the Default Schema assigned by Microsoft SQL Server (which is dbo)....you simply need to match the 'Default Schema' with the username ( if username is X--> the default schema should be X as well)

If a Login has been granted "sysadmin" privilege, it automatically becomes a DBO and its schema is taken as 'dbo'.

NOTE: This is same for both DBO-Schema and SDE-Schema geodatabases.

[TimHayes]

Tim,

As mentioned in the below link, "Remember, the user name and schema name must be the same if this user is going to own data." -- Point #14

Adding database-authenticated logins to a SQL Server database:
http://resources.arcgis.com/en/help/main/10.2/index.html#//002q0000002m000000

Instead of trying to match the username with the Default Schema assigned by Microsoft SQL Server (which is dbo)....you simply need to match the 'Default Schema' with the username ( if username is X--> the default schema should be X as well)

If a Login has been granted "sysadmin" privilege, it automatically becomes a DBO and its schema is taken as 'dbo'.

NOTE: This is same for both DBO-Schema and SDE-Schema geodatabases.

I understand. Here is my situation now:

I am the dbo. My database user name is dbo, the schema is dbo. I can do what I need to do no problem. It works.

I have one of my GIS Specialists, I want him to be able to add data/edit data in the Geodatabase. Currently, his database user name is X, and his schema is dbo. Because the database user name is different from the schema, he cannot do anything. He already has the roles of db_writer, db_reader, and db_owner. How do I get him onto the database user name dbo?

[AsrujitSengupta · Accepted solution]

Tim,

Thats kind of an incorrect workflow that you are following....making the software believe that you are a user with DBO privileges.

If you want all the users to load data as "DBO" (I believe you want this so that the naming convention in your sde gdb shows as db_name.DBO.xxx), you will have to grant them the "sysadmin" privilege (which is the highest possible privilege in SQL Server and not only grants complete access to that particular geodatabase, but any geodatabase on that SQL Server instance).

Have a look at the below article from Microsoft explaining dbo and db_owner:
Server and Database Roles in SQL Server:
http://msdn.microsoft.com/en-us/library/bb669065(v=vs.110).aspx

The dbo, or database owner, is a user account that has implied permissions to perform all activities in the database. Members of the sysadmin fixed server role are automatically mapped to dbo.

The dbo user account is frequently confused with the db_owner fixed database role. The scope of db_owner is a database; the scope of sysadmin is the whole server. Membership in the db_owner role does not confer dbo user privileges.

So if you want the users to load data as DBO, then you'll have to grant them the "sysadmin" server role.
In SQL Server Management Studio--> Connect to Instance as a Admin user--> Instance Security--> Logins --> R-Click on the Login --> Server Roles--> Sysadmin

[TimHayes]

Do you mean that if I want more than one person to be able to edit a SQL Server Enterprise Geodatabase we all need to be the sysadmin? I thought these permissions can be granted on a database by database basis?

[AsrujitSengupta]

No, I wrote that for creating/loading of new data.....not for editing existing data.

For editing existing data, you just need to grant the Logins sufficient permissions either on the database (db_reader, db_writer) or on individual data (ArcCatalog privileges)

However, to create/load new data using the DBO schema----you'll have to grant sysadmin to the other users.

That is the reason, its advisable to create a common Login for loading/creating new data into the sde gdb and I'll suggest you the same, unless you specifically need to load it with DBO-Schema