Geodatabase Schema Organization

669
2
Jump to solution
11-11-2022 03:11 PM
ACrateau
New Contributor III

In designing a geodatabase structure for our organization (using SQL Server), my goal is to use different schemas to represent the departments within our org.  For example:

{db}.Dept1.Dataset

{db}.Dept2.Dataset

My understanding is that we will need separate data owner logins to correspond with each distinct schema.  I have seen straight forward examples of setting this up using SQL Server logins and sde-schema.  Our organization requires the use of windows accounts (AD) and logins on the SQL Server. 

Is it possible to map a windows account, e.g. "DOMAIN\Dept1-dataowner-acct" to a custom schema name like "Dept1"? 

Tags (3)
0 Kudos
1 Solution

Accepted Solutions
MarceloMarques
Esri Regular Contributor

Yes, it is possible to use windows domain login as a data owner user in SQL Server, but I strongly recommend you to avoid this kind of setup, instead create an usual SQL Server data owner user, create the schema, load the data, then for each department either create an "editor" SQL Server Login or add a windows domain login and grant select, insert, update, delete on the tables of the data owner user to the "editor" user, by doing this you enhance security, make it easy to move the database in the future, restrict access to the data owner schema, etc.

Please, read my database guide book from SQL Server in the link below, see the "Production Mapping" Guide Book for example, and there you will also find my database template scripts for SQL Server that you can download and customize as needed.

Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov

View solution in original post

2 Replies
AvinashPatel
New Contributor II

@ACrateau 

I have not tried that way but used SQL User as data owners and domain account as editor or viewers with a set of permissions of uses cases.
Will check for the possibilities and update you.

MarceloMarques
Esri Regular Contributor

Yes, it is possible to use windows domain login as a data owner user in SQL Server, but I strongly recommend you to avoid this kind of setup, instead create an usual SQL Server data owner user, create the schema, load the data, then for each department either create an "editor" SQL Server Login or add a windows domain login and grant select, insert, update, delete on the tables of the data owner user to the "editor" user, by doing this you enhance security, make it easy to move the database in the future, restrict access to the data owner schema, etc.

Please, read my database guide book from SQL Server in the link below, see the "Production Mapping" Guide Book for example, and there you will also find my database template scripts for SQL Server that you can download and customize as needed.

Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov