Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Cost Effective SQL Server Transparent Data Encryption - Free Webinar
 

SQL Server Management Studio Error 916


By:   |   Last Updated: 2012-09-10   |   Comments (10)   |   Related Tips: More > SQL Server Management Studio

Problem

When connecting to SQL Server using Management Studio (SSMS), with limited permissions, you do not see any user databases or receive Error 916 when expanding the database list from Object Explorer. The error message is "The server principal "Login Name" is not able to access the database "database name" under the current security context. (Microsoft SQL Server, Error: 916)."  In this tip I will explain the root cause of this issue and how to fix it.

Solution

You can experience Error 916 when you connect to SQL Server using SSMS with limited access such as db_datareader, db_datawriter or db_owner for a specific database.

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 for SQL Server Error 916

Here are some possible reasons for SQL Server Error 916:

  • A login does not have permission to view data of a column selected in the header
  • A database is offline and/or its collation is NULL
  • Multiple databases with different collations are on the instance and SSMS is unable to retrieve the collation because a database is configured to auto-close

Reproduce and Correct SQL Server Error 916

Step 1: Connect the SQL Server using SSMS.

SSMS Login

Step 2: Click on Object Explorer Details option from the View Menu or press F7.

SSMS View Menu to select the Object Explorer Details Menu

Step 3: Double click on the Databases folder.

Object Explorer Detail View

Step 4: In the image below you can see there are multiple columns like Name, Policy Health State, Collation, Data Created, Last Backup Date, Size (MB), Data Space Used (KB), Index Space Used (KB), etc. in the column header, but no user defined databases are being displayed.

Object Explorer Detail View with Column Headers and no databases displayed

Step 5: Before fixing the error, let's see if we can find the root cause of the issue. To do so I connected SQL Server again with SSMS using the SA account and found that the collation is NULL for two user defined databases as shown below.

Object Explorer Detail View with databases offline

Step 6: To resolve the issue above, right click on column header in the Object Explorer Details window and un-check all of the optional columns.  Next click the refresh icon and you should see your databases. In most cases just by unchecking the COLLATION option will fix the issue.

Object Explorer Details Column Header Pop Up Menu

Step 7: I have unchecked the columns and you can see the databases in the Object Explorer Details window as shown below.

Object Detail Menu with minimal columns selected
Next Steps


Last Updated: 2012-09-10


next webcast button


next tip button



About the author
MSSQLTips author Jugal Shah Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, July 01, 2015 - 9:58:06 AM - Enes Taha SELEK Back To Top

Thank you very much. I hated this error. Finally solved. Thanks again.


Wednesday, April 29, 2015 - 7:37:13 AM - Gerson Guilhem Back To Top

It worked! Thanks a lot!


Sunday, March 08, 2015 - 8:42:21 PM - Faisal Back To Top

Man, Helped alot....Thank you. :)


Monday, December 01, 2014 - 10:42:20 AM - JuanAguirre Back To Top

Review the "Autoclose" option for the database that is described on the error. Enabling this option causes that the status for the database is NULL.


Thursday, September 04, 2014 - 6:06:11 AM - Dinesh DBA Back To Top

 

Hi Jugal,

I did mension above following error came

The database test is not accessible

What  to do ?

Regards,

Dinesh DBA


Friday, July 18, 2014 - 5:01:35 AM - Sajal Bagchi Back To Top

We had exactly the same issue and even after unchecking te collation, the user was still getting the error. The only difference we had was , he was getting error for msdb database. Referring this link http://support.microsoft.com/kb/2539091, we gave connect permission to the guest user and the issue was resolved.


USE msdb;

GRANT connect TO guest;

GO


Wednesday, March 27, 2013 - 1:02:29 AM - shalini Back To Top

Hi Jughal,

 Your article explains the things very clearly. I was breaking my head when i got the error. thank you so much.

 


Friday, January 11, 2013 - 4:58:57 AM - shibu Back To Top

thank you dear i was breaking my head to solve this issue 


Saturday, January 05, 2013 - 2:35:55 AM - Dinesh Back To Top

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

    There was windows login.I login by SA then it was working sucessfully.


Monday, September 10, 2012 - 9:34:09 AM - Gopalakrishnan Arthanarisamy Back To Top

Excellent article with nice screenshot reference. Thanks Jugal Shah.

 

Gopalakrishnan Arthanarisamy

Unisys, Bangalore.


Learn more about SQL Server tools