Fix Microsoft SQL Server Management Studio Error 916

Introduction to SQL Server Error 916

While connecting to SQL Server database using SQL Server Management Studio with limited permissions you may face MS SQL Server error 916. In this article we will discuss how to fix the error 916 in SQL Server.

Error message

The server principal "Login Name" is not able to access the database "database name" under the current security context. (Microsoft SQL Server, Error: 916).

Reasons of MS SQL Server Error 916

In this section I have discussed the possible reasons responsible for the error no 916 in SQL Server.

1. User with limited permissions trying to view the column data in a selected header.
2. It is possible that the database is in offline mode or it may also possible that the collation is NULL.
3. Several databases with different collations are on the same instance, that’s why SQL Server Management Studio is unable to retrieve the collation.
4. The database is configured to auto-closed state.
5. It’s also possible that the database gets corrupted due some malware, improper shutdown or the database has gone in suspect mode.

Fix Error Number 916 in Microsoft SQL Server

There are two methods to fix the error. The first method is via SQL Server Management Studio and the next one with the help of T-SQL. Let’s take a tour at step by step solution of this error.

Fix error 916 via SSMS

1. Firstly, connect the SQL Server instance having valid credential using SSMS.
2. Now, go to the Object Explorer Details followed by the View Menu or you can press F7 for the same.
3. After this, open the Databases folder.
4. Now you can see the properties of the database like Database Name, Collation, Data Created, Last Backup Created Date, Size in (MB), data space in (KB)etc. but in the column header, you will see there are no user defined databases.
5. Now, go to the object explorer > right click on them to open the column header > right click on the column header and un-check all of the optional columns.
6. After doing this, now click the refresh button and you will be able to see your database.

Note: In most of the cases the un-checking of the COLLATION option automatically fix the issue.

Fix error code 916 via TSQL Query

You can connect to the database via Transact-SQL. Grant the specific login access to the named database.

Syntax:

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

Conclusion

In the present article we have discussed about the Microsoft SQL Server error 916. This article describes why the error occurs and how can we fix this issue via SQL Server Management Studio and Transact-SQL.