Can't connect to SDE database with db_datareader permissions.

[SeanTondre]

We're currently running ArcGIS 10.4.1 and SQL Server 2012.  I needed to do some testing so I created a new user in SQL Server Management Studio with a database login and assigned db_datareader access.  We have 4 databases running in this instance, and I successfully connected to 3 of the databases.  However, I received the following error on the 4th "Failed to connect to the specified server.  Do you want to continue?  Operation Failed". The only thing that was noticeably different was that in the failed database db_datareader did not have an owned schema (whereas the other 3 both had "db_datareader" checked).  I checked it and I received the same error message.  

Let me know if I can provide any additional info.  Any help would be greatly appreciated!

Sean

[Asrujit_SenGupta]

Usually the CONNECT permission is all you need to connect to a Database. Too see data you need 'db_Datareader'.

So, check whether the User-Mapping for that user was correctly done for that database. If it is a newly created Database login, maybe delete it and recreate the login and then check again.

Privileges for geodatabases in SQL Server—Help | ArcGIS Desktop 

[SeanTondre]

Thanks for the reply.  I was able to connect to the database by applying only CONNECT permissions.  There are still some things that don't look right to me.

The 3 other databases, with CONNECT only permissions, showed relationship classes.  However, the database in question was empty (even though there are multiple relationship classes just like the other databases).  So I guess it isn't connecting to the database that is the problem rather viewing permissions once connected.

With CONNECT only permissions I next added Feature Dataset permissions through ArcCatalog.  Three of the databases seemed to work correctly giving me access to the particular feature dataset (see screenshot):

The database in question allowed me access but something is wrong:

That should all be contained within a feature dataset (and why is it showing the historical tables and version views?).

Following your instructions I created a brand new login and got the same results as before.

[Asrujit_SenGupta]

Is that a simple Database or an Enterprise geodatabase?

If it is a geodatabase, do you observe the same behaviour when connected as the SYSADMIN login? Rather have you tested with multiple logins and faced the same behaviour? 

[SeanTondre]

It is an enterprise geodatabase.  When I grant the login sysadmin server role then I am able to view all data in the gdb (but also have insert, update, and delete permissions).  The same is true if I assign the user db_owner permissions.  But when I assign SELECT only permissions I can't connect.  It only happens with this one database so I'm guessing there is something wrong with the actual database.

[Asrujit_SenGupta]

Seems to be an issue with the default permissions granted to the PUBLIC role. I have a list of the default permissions granted to the PUBLIC role for an sde 10.3 gdb, but somehow I can't attach a text file here it seems.

I would suggest contacting Esri Tech Support so that they can take a closer look at this.

[JeffWard]

Sean Tondre any resolution to this? I'm experiencing the same issue.

[SeanTondre]

Sorry, I don't think there was.  My next step was going to be moving the data to a fresh database but we didn't have an immediate need for reader access so I left it.