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.
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.
Step 2: Click on Object Explorer Details option from the View Menu or press F7.
Step 3: Double click on the Databases folder.
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.
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.
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.
Step 7: I have unchecked the columns and you can see the databases in the Object Explorer Details window as shown below.
Next Steps
- Review your SQL Server database settings to be sure your databases are configured correctly and being displayed for your user community.
- Check out these related tips:
Biography
Jugal Shah is the founder of SQLDBPool.com, where he is writes articles on SQL Server Administration and Development. Jugal has 8+ years of extensive experience in SQL Server Database administration and development. Jugal has worked on SQL Server 2000, 2005, 2008 and 2008 R2. In his current assignment, Jugal is managing large critical databases. His expertise is in database security, performance tuning and implementing high availability solutions. He can be contacted at jugal.shah@sqldbpool.com. He has been recognized as a SQL Server MVP since 2010.
- MSSQLTips Awards: Trendsetter (25+ tips) – 2013
