Wondering how to fix SQL Server Error 916. We can help you.

Often while connecting to SQL Server database using SQL Server Management Studio with limited permissions, we may face Microsoft SQL Server error 916.

Here at Bobcares, we often get requests from our customers to fix similar Microsoft SQL server issues as a part of our Server Management Services.

Today, let’s see how our Support Engineers fix this error for our customers.

What causes SQL Server Error 916

A typical error message is given below:

Following are some of the causes for this error:

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 fix 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

Steps for Fixing the 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.

Steps for Fixing error code 916 via TSQL Query

We can connect to the database via Transact-SQL. Grant the specific login access to the named database TEST_DB, for instance.

We can use the following syntax:

USE msdb;
GO
GRANT CONNECT TO [TEST_DB/BOB];

Now we can try either of the following to fix this error.

1. Disabling Collation

1. First, we need to select the Object Explorer Details under the View section within the SSMS.

2. Then right-click on Column Header and deselect Collation.

3. 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? We can help you]

Conclusion

In short, we saw how our Support Engineers fix SQL Server Error 916 for our customers.