AnsweredAssumed Answered

Multiple Scheme Based Geodatabse on Microsoft SQL Server

Question asked by Lcaraway34_1 on Oct 12, 2017
Latest reply on Oct 12, 2017 by asrujit_pb

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.

Outcomes