Two different "sde" users in two geodatabases in the same instance in SQL Server

[JesúsdeDiegoAlarcón]

Dear all

We are trying to deploy two different geodatabases in the same SQL Server instance. One will be the DEV geodatabase and the other the PRO one.

We would like to have two different "sde" users with two different passwords (don't matter if the name of the user is not "sde"....). However, till know we do not find the right way to do this....Any idea? Is this possible?

Best regards

Jesús de Diego

[George_Thompson]

It does not look like you could have an "SDE" user have different PW on the same instance. At least from the Esri perspective.

It looks like if you have an SDE schema Enterprise Geodatabase (EGDB) then you have to have an SDE user.

https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/create-enterprise-geodatabas...

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/setup-geodatabase-...

@MarceloMarques - any thoughts?

[JesúsdeDiegoAlarcón]

Hi George

First of all, thanks for your support. In addition, I am almost new to SQL Server administration (more experience with Oracle....) so maybe some of my comments can sound a bit strange from a SQL Server perspective...

At least what we were investingating was:

- Having a sde user and schema in one of the databases (let's say, the traditional way....). We have used the "Create Enterprise Geodatabase" tool for creating the geodatabase and worked fine.

- Having a different loging mapped to the dbo user for the second one (as it is stated here: https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-sql-server/comparison-geodatabase-ow... ).In this second case we used the "Enable Enterprise Geodatabase" tool but we got:

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The specified schema name "sde" either does not exist or you do not have permission to use it.

Why does the "sde" schema still appears here?  Maybe we did not configured this new login correctly? If so.. how to do that? Or maybe we misundestood something? Or maybe, as you mention, what we want is not possible (but taking into consideration that we will accept having a sde and sde_stg ,e.g., as geodatabase administrators).

Thanks in any case

Jesús de Diego

[George_Thompson]

Did you create a user that is mapped to database owner for the one that you used the Enable tool on?

• Add or create a login that is mapped to a database user named sde or to a database user that is the database owner.

I do know that you cannot have the SDE user mapped to a schema that does not match in SQL Server. I am wondering if the mapping is the issue.

Did you give that user the db_owner pemission on the database?

Your question makes sense to me seeing that you mentioned you are familiar with Oracle.

[AyanPalit]

@JesúsdeDiegoAlarcón The underlying RDBMS dictates the users/authentication and Geodatabase inherits those properties.

Refer to the link : Create a database user in SQL Server

Your use case: SQL Server authentication wherein password is validated by SQL Server at master database. So you cannot have two different passwords in the same SQL instance. Enterprise best practice will be to split the DEV and PROD into two different SQL instance and you can assign two different passwords in this case.

[VinceAngelo]

Not to pile on, but this is not possible.  Each instance can only have one 'sde' login.

The only way to have two different 'sde' logins is to have two different database instances (running on different machines or on one machine with two different database ports (the second on other than the SQL Server default of 1433).

- V

[MarceloMarques · Accepted solution]

@JesúsdeDiegoAlarcón 

This is very simple to configure.

1. create the databases following my database template scripts for SQL Server, use the Production Mapping database guidebooks, they are generic enough for any industry to use it.

Mapping and Charting Solutions (MCS) Enterprise Da... - Esri Community

 ArcGIS Pro 3.x & ArcMap 10.8.x  June 21, 2022  *New

- the template scripts have very advanced rdbms and advanced geodatabase best practices!

2. Download the zip file and unzip.

3. For example, to create a geodatabase named "pm"

...\db_sqlserver_sde_admin_mcs\db_admin\1_db_create\1_db_create_pm

4.  map the "sde" login to the database user "sde" then create the geodatabase repository
for more details see "5a_db_create_sde_repository_pm_NEW.sql"

USE [pm]
GO

ALTER USER sde with LOGIN=sde
GO

5. after the arcsde geodatabase repository is created then you can map the login "sde_pm" to the database user "sde".

USE [pm]
GO

ALTER USER sde with LOGIN=sde_pm
GO

6. Eccolo!!! Grazie Mille!!!

- each sde_<db-name> login has a different password

- each sde_<db-name> login maps to the database "sde" user.

Prego! Mi Piace Aiutare !!!

7. Another option is to use "Contained Database Authentication".

This way you do not use logins anymore, each SQL Server database stores its own users and passwords.

...\db_sqlserver_sde_admin_mcs\db_admin\0_install_software\1_windows\1_install_sqlserver.txt

"--4. Contained Database Authentication"

...\db_sqlserver_sde_admin_mcs\db_admin\1_db_create\1_db_create_pm\4_db_add_users_pm.sql

8. Please also read for more best practices.

• Database Connections Best Practices
• Guide Books ArcGIS Pro

I hope this answers your question.