failed to connect to the specified DBMS instance(sqlserver)

[baohuachu7]

When I add an acount(not sde) with sde schema to a geodatabase,   the sde account of other geodatabase can not access geodatabase. With error info :

Underlying DBMS error, exception has occured,

Failed to connect to the specified DBMS instance.
A database connection exception has occurred.C Underlying DBMS error[42000:[Microsoft][ODBC Driver 17 for SOL Serverl[SQL Server]The SELECT permission was denied on the object "'SDE_version',database swyGDB, schema sde No extended error].

[George_Thompson]

How are you adding the new account: Pro tool / via SSMS?

Can you also expand on what "with sde schema to a geodatabase" means?

From the error message, it seems that permissions on the sde schema are not public or were changed.

[baohuachu7]

Hi George,

     The operation is done  in SSMS.  I have one acount userA belone to GDB A.  And want to authorize this  account to GDB B by doing as  this: 

     In SSMS , security->logins ->select userA->properties->user mapping->check GDB B. 

   and in the "database role membership of GDB B" ,checked "db_datareader,writer,owner,securityadmin..." checked.  

In GDB B->security->users->select UserA->properties-> in the  configuration panel check the "owned schema" with "sde,...". Then in the end the above error pops up.

Now the above issue is solved by sleect UserA->user mapping->uncheck the GDB B.

[VinceAngelo]

You really shouldn't have any need to create any logins that map to the sde schema, because you should never, ever load user data with the sde schema. Enterprise geodatabases actually have a requirement that the schema be the same as the login/user for any tables to be edited with ArcGIS tools (the only exception is when the geodatabase is owned by DBO, at which point it really has no security model at all). There is a Create Database User (Data Management) tool to help you create working enterprise geodatabase logins.

This has the feel of an XY Problem. What were you attempting that made creating multiple logins that mapped to the sde schema seem necessary? Maybe we can help with that problem, instead.

- V

[baohuachu7]

Hi Vince,

   Thank you for your help.  The GDB owned by DBO is the GDB which is created using sa account connection?  

  If I want an user with the privilege to edit data in  both GDB A and GDB B ,but not modify feature class level , what is the steps to do this?

[George_Thompson · Accepted solution]

I would recommend using AD groups. Add them to the SQL instance, map them to GDB A & B (connect only), then in Pro assign the editing permissions to the groups (this must be done as the feature class owner).

That should allow them to just edit data in the allowed feature classes (or related objects).

Here is some doc to help with the process: 

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/user-accounts-grou...

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/add-users-sqlserve...

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/privileges-sqlserv...

[KimberleeHermann1]

Have you tried reinstalling the ODBC Driver?