Failed to connect to database. Underlying DBMS error[42000:]The SELECT permission was denied on the object 'SDE.version', database 'DB_name', schema 'sde'.No extended error

Error Message

During the installation and configuration of an enterprise geodatabase, grants are made to the Public role on tables for Delete, Insert, Select, and Update permissions. Additionally, some grants are given to the PUBLIC role to add EXECUTE to various SDE system tables.

These permissions are required to provide access to geodatabase functionality, such as viewing certain system tables and performing administration tasks.

Starting at ArcGIS for Desktop 10.1, the permissions are initially granted when using either the Create or Enable Enterprise Geodatabase tools.

When connecting to an enterprise geodatabase the following error may occur:

Error:   
Failed to connect to database. 
Underlying DBMS error[42000:[Microsoft][ODBC Driver 11 for SQL Server][SQL Server] The SELECT permission was denied on the object 'SDE_version', database 'DB_name', schema 'sde'.No Extended error.]

Cause

The error is generally caused by missing public role permissions in SQL Server, namely the Select permission to the SDE_version table.

Solution or Workaround

The following scripts generate grant statements for the public role when run in SQL Server Management Studio. Both scripts generate a similar number of grant statements. These grant statements can be copied into a new query window and run as a user with sysadmin privileges.

Warning:   
Proceed with caution. The troubleshooting steps in this article should be guided by Esri Support Analysts and it should never be performed on a production instance without taking a high level of precaution. It is recommended to test this first on a separate staging server and make a full backup of database before implementation and ensure that the backup and restore workflows have been thoroughly tested.

SELECT CASE protecttype 
WHEN 204 THEN 'GRANT'
WHEN 205 THEN 'GRANT'
WHEN 206 THEN 'DENY'
END + ' ' +
CASE action 
WHEN 224 THEN 'EXECUTE'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 193 THEN 'SELECT'
WHEN 197 THEN 'UPDATE'
END + ' ON ' +
user_name(o.uid) + '.' + o.name +
' to ' + user_name(p.uid)
FROM sysprotects p
JOIN sysobjects o ON p.id = o.id
where p.uid = 0
order by o.name

SELECT 'GRANT ' + permission_name COLLATE DATABASE_DEFAULT  
+ ' ON ' + user_name(o.schema_id) + '.' + o.name + ' TO ' 
+ user_name(p.grantee_principal_id) COLLATE DATABASE_DEFAULT + CASE
WHEN STATE = 'W'
THEN ' WITH GRANT OPTION'
WHEN STATE = 'G'
THEN ''
END 
FROM sys.database_permissions p 
JOIN sys.objects o ON p.major_id = o.object_id 
where user_name(p.grantee_principal_id)  = 'PUBLIC' 
order by o.name