Certain users can't Review/Rematch Addresses using SDE Locator

[JoeHeggenstaller]

I may have found a bug (or a feature) when using geocoding locators stored in SDE. We are in an ArcGIS 10 SP2 environment running on MS SQL Server 2005. We have a user I'll call "GISAdmin" that has SQL Database Role
Membership as a "db_datareader", "db_datawriter", "db_ddladmin", "db_owner". We have a user I'll call "GISUser" that has SQL Database Role Membership as a "db_datareader" on all datasets by default. We use Database
Authentication when making a Spatial Database Connection in ArcCatalog.

Using ArcCatalog I create an ArcGIS 10 geocoding locator, called Addr_Pnt_Locator, using the GISAdmin user in the SDE database. The locator reference dataset is in the same SDE database. Following the ArcGIS 10 Help instructions "Accessing an ArcSDE address locator" I grant the GISUser "Allow this user to use the selected object(s)" privileges to the newly created locator. When I review what has happened in SQL Server I see that the Addr_Pnt_Locator_LOX table now has the GISUser added to its permissions with SELECT permissions.

I then open ArcMap, bring in a File GDB table to geocode, open a connection as the GISUser to the SDE database and add the Addr_Pnt_Locator to the project. When I geocode the table it works fine, until the point where it asks if you want to Review/Rematch. If you choose "yes" you receive the following error "There was an error reading the geocoding information from this dataset." You can't Review/Rematch after the fact using the toolbar button, you receive the same error.

If I do the same test, but use a connection to the database as the GISAdmin user I can Review/Rematch without a problem.

As another test I used SQL Server Management Studio to Grant the GISUser the following additional permissions on the Addr_Pnt_Locator_LOX table: Alter, Delete, Insert and Update. When I attempted to Review/Rematch I received the same error message. I do not want to go so far as to give the GISUser SQL Database Role Membership as a "db_datawriter" because we don't want this user to be able to edit the data in this database.

I know that this error message came up in another forum post, but I don't think the "export unmatched records and re-geocode" is an adequate solution. If the record didn't match the first time I don't think it's going to magically match successive times through the
process. The Review/Rematch functionality should work in the interface so that the human element can triage ambiguous/wrong addresses.

I have come up with a work around, which involves creating an ArcGIS Server Geocoding Service from the address locator and then have the GISUser consume the service in ArcMap. It does allow you to Review/Rematch addesses. If there is anyone out there that has seen this and can provide a solution that would be great. Thanks!

[JoeBorgione]

This is a total WAG (wild a** guess), but what are the permission/privileges on the geocoding results point feature class?  If you are working in the SDE environment, wouldn't that feature class need to be registered as versioned?

Sorry this isn't much help but might be worth looking into.  While I use an SDE locator, I typically write the results to a pgdb or a fgdb so I've never encountered what you are experiencing.

[JoeHeggenstaller]

Joe B.,

Thanks. That's a great thought, but the weird things is that I am writing the results FC to a FGDB, not to the SDE DB. I was mucking around with permissions on the *_LOX table because I figured as a shot in the dark that maybe the geocoding process needed to "temporarily" write something in the LOX, but I threw the question out here hoping somebody from ESRI would give me a clue.

Thanks again!

Joe H.

[JoeBorgione]

But can the users edit any other fc in that fgdb?  If they can then it's weirder than a permissions issue, right?

[JoeHeggenstaller]

Joe B.

I haven't tested if the GISUser can edit a FC in a FGDB because that user only exists to connect to the SDE database and isn't a Windows Active Directory/Computer User. It would be my understanding, maybe incorrectly, that when a user is interacting with the FGDB they would not be connecting to it as the GISUser, but as their Windows user (Bubba). Bubba can do whatever they want to the FGDB as long as they have permissions to the directory (locally or network share). I would think that Bubba is writing the geocoded results into the FGDB, but is just using the GISUser to connect to the SDE database. I tested this theory by creating a GISUser account on my local machine and then giving it full permissions to the folder which contains the FGDB into which I'm writing data. No luck, still getting the same error message...

Also, I'm not aware of being able to set permissions on a FGDB or FC's within a FGDB. I poked around ArcCatalog and didn't see where it could be set. Am I missing something?

Thanks for the suggestions!

[BobLaverty]

Looking at it as DBA (Oracle), I'm wondering whether the rematch is trying to establish an updateable join between the output FGDB FC and the locator in SDE.  It might be looking to have 'write' privileges on the locator table that it doesn't need, but the database isn't smart enough to know that.  It would explain why it works with the other ID.

[JoeHeggenstaller]

Bob,

Good thought, but I went down the path (for testing purposes only on our "test" server) of giving the GISUser all of the same Database Role Membership permissions as our GISAdmin user in order to suss out if something was getting written (updateable join as you mentioned?) into SDE behind the scenes. I still get the error.

Thanks,

Joe H.

[BobLaverty]

Joe B. might have touched on it then.  SDE version control might be preventing the locator table from joining in an updateable view.  It's locked for the GISuser but not the GISadmin.

Another shot in the dark.  Good luck.

[JoeHeggenstaller]

Bob, Joe B.,

Thanks for the continued support and ideas. I took another test DB, deleted all the versions that we had on it so only DEFAULT was present. I created the address locator as GISAdmin. Used GISUser to geocode, but still couldn't rematch. I'm out of ideas.

Thanks,

Joe

[JoeBorgione]

Probably time to contact tech support....