This article covers method to resolve Microsoft SQL Server Error 916 for our customers.
The main problem is due to bugs present in SQL Server Management Studio that prevents the user to connect to the database and refuses to display the database list.
How to solve Microsoft SQL server error 916:
If you receive the following error while using Microsoft SQL Server Management Studio 2008:
The server principal "username" is not able to access the database "dbname" under the current security context. (Microsoft SQL Server, Error: 916).
Causes of SQL Server Error 916:
1. The user is not permitted to view the data of selected column within the database.
2. The database is currently not available.
3. There are multiple columns such as Size, Space available, Data Space Used, Default file group, Index Space Used, Mail host, Primary file path and user has added at least one of them to the list of Object Explorer Details.
4. If the Auto Close option for the database is enabled, then Collation column cannot be retrieved by SQL Server Management Studio (SSMS).
5. For a database, Collation column contains NULL.
In order to view the granted permissions for the guest-user, the following command is helpful if run by a member of a Sysadmin fixed server.
The query is as follows:
SELECT prins.name AS grantee_name, perms.*
FROM sys.database_permissions AS perms
JOIN sys.database_principals AS prins
ON perms.grantee_principal_id = prins.principal_id
WHERE prins.name = ‘guest’ AND perms.permission_name = ‘CONNECT’;
On running the above command, a table is presented to the user containing all the attributes of guest user.
However, an empty result shows that guest user is disabled in the database and again the SQL Server error 916 will be displayed.
To overcome the error you may perform the following available solutions.
To fix this SQL server error 916:
1. In Object Explorer, click databases.
2. Display "Object Explorer Details" (F7) or "View –> Object Explorer Details".
3. Right-click the column headers and deselect "collation".
4. Refresh the databases.