Geodatabase Schema Organization

[ACrateau]

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"? 

[AvinashPatel]

@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 · Accepted solution]

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

• Database Connections Best Practices