Multiple Scheme Based Geodatabse on Microsoft SQL Server

446
1
10-12-2017 01:11 PM
LoganCaraway
New Contributor II

Hello, 

I have had a request to redesign our enterprise database so that it can leverage both scheme and windows based security groups. The database is to exist on Microsoft SQL Server. For the sake of the conversation lets say we have two groups that need to access this. Mapping is to be the generic group that is to contain basic mapping data. The second will be called land. The Land group will need to have access to more sensitive data that we do not want the average user to see. 

Before I explain what I have attempted, let me explain what the target outlook would be.  We would like to be able to have 1 database with the data sepperated out by scheme. For example:

1) GeodatabaseName.Mapping.Roads

2) GeodatabaseName.Mapping.States

3) GeodatabaseName.Land.LandOwners

To where people in the active directory group mapping can see 1 & 2 and land group can see 3. 

Any thoughts as to tackle this?

At the risk of muddying the water, the next section will explain what I have tried. 

I have attempted this by adding the relative groups as Server Logins and have mapped them appropriately to specific database users. For example, our active directory has a group "DOMAIN\Mapping". This group is now a server login that is mapped to also be a user of the Geodatabase. Next I have created both a Scheme and a Role both called "Mapping". The Role owns the Scheme and the User is added as a member to the role mapping. Same process was done for the Land Scheme.

I can confirm that his has the desired effect from inside of MSSQL Studio by confirming databases created in either scheme are only visible to the appropriate users groups. 

Heres the issue, if i want the user tot then be able to create a new feature class i get the error "Database User Name and current schema do not match" and appears to be trying to name the new table using the username as a scheme.

0 Kudos
1 Reply
Asrujit_SenGupta
MVP Regular Contributor

Add logins and users to SQL Server—Help | ArcGIS Desktop 

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.

I guess this explains that what you are observing is "As Designed"