Are you trying to resolve Microsoft SQL Server Error 916 ?
This guide is for you.
Sometimes in the process of connecting to SQL Server database using SQL Server Management Studio with limited permissions, we may face Microsoft SQL Server error 916.
Here at Ibmi Media, as part of our Server Management Services, we regularly help our customers to resolve related Microsoft SQL server errors.
What triggers Microsoft SQL Server Error 916 ?
The main reasons for this error are:
1. When a user with limited permissions tries to view the column data in a selected header.
2. At times, the database may be in offline mode.
3. Several databases with different collations are on the same instance, that is why SQL Server Management Studio is unable to retrieve the collation.
4. The database is configured to an auto-closed state.
5. Sometimes the database may get corrupt due to some malware, improper shutdown, or when the database goes in suspect mode.
How to resolve Microsoft SQL Server Error 916 ?
Following are the two methods to fix this error:
1. Via SQL Server Management Studio (SSMS)
2. With the help of T-SQL
Process to fix error 916 via SSMS:
1. Firstly, connect the SQL Server instance having valid credentials using SSMS.
2. And go to the Object Explorer Details followed by the View Menu or press F7 for the same.
3. After that, open the Databases folder.
4. Then go to the object explorer and right-click to open the column header.
5. After that, right-click on the column header and uncheck all of the optional columns.
6. Finally, click the refresh button.
Now we will be able to see the database.
Often unchecking the COLLATION option automatically fixes the issue.
Methods to fix error code 916 via TSQL Query:
We can connect to the database via Transact-SQL. Grant the specific login access to the named database.
We can use the following syntax:
USE msdb;GOGRANT CONNECT TO [TEST_DB/BOB];Now we can try either of the following to fix this error.
1. Disabling Collation
i. First, we need to select the Object Explorer Details under the View section within the SSMS.
ii. Then right-click on Column Header and deselect Collation.
iii. Finally, refresh the server and operate a database.
2. Disabling the Auto Close setting of the database
Here we need to check the Auto Close setting of the database, set it to False, and proceed further.
[Need assistance in fixing SQL errors? We can help you. ]
Conclusion
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:
USE msdb; 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’; GOOn 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.
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:
USE msdb; 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’; GOOn 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.
