SQL Server - 2008/2012 - Not able to view list of databases in SSMS - Error 916 - not able to access database under current security context

By
Last month, a user comes up to me with a very unusual problem. He was not able to see list of databases under the "Databases" group\folder when connecting to a remote SQL Instance using SSMS (SQL Server Management Studios).
He was getting the below error:










Now, before coming up to the solution let me have some pointers about this issue or case as error message "not able to access database 'xyz' under the current security context" can occur due to several reasons, but we are intend to discuss only one particular case in this post.

The pointers are as follows:
  1. Login is part of "public" server role and also had adequate access to the database, which user wanted to use. Also, no granular level deny was applied to the login.
  2. Though the login didn't had any explicit permissions to the database for which the error was thrown but the user was also not intended to use the database.
  3. Considering above points and under default settings user should be able to view the list of the databases.
Apart from the above points one very important point to note here is that user was accessing a SQL 2012 instance using SQL 2008 SSMS, which didn't had any Service Pack installed and this is what causing the error here as well. Though this can be corrected by installing appropriate service pack but there is also a work-around available which you can use to overcome this situation cause and good thing is that this step can be implemented very quickly and also doesn't require any special privileges unlike installing service pack which is both time consuming and will require a person with elevated permission to install it on user's machine.
What all you have to do is go to "Object Explorer Details" tab on the right hand side of the SSMS (Use "F7" in case you're not seeing it) after selecting "Databases" folder in "Object Explorer". On the "Object Explorer Details" you'll see various tabs like "Name", "Policy Health State" etc. Right click on column area and go to select columns and from there uncheck "Collation" option (also uncheck "Compatibility Level" if error persists). Once unchecked hit "F5" and for most of you the error is gone and you're now able to get the database list and work normally.








If in case it didn't worked then we have no options left other than applying the service pack or use the same version SSMS, however for me it worked each time and hope it does for you too.

MCSA Certified SQL Server DBA with sublime development skills to automate regular tasks and interest in following and discussing trending technologies, well-being and spirituality.

Comments

Post a Comment

Popular posts from this blog

SQL Server: SQL Server services not starting. TDSSNIClient initialization failed with error 0x139f, status code 0x80. Reason: Unable to initialize SSL support.

By
Hi All, and welcome back. As the computing power and networking is increasing day by day so is the need of more safer and stronger encryption channel methods and algorithms. And we want our databases and database server to use the same as well. But sometimes implementing them may cause undesired results like SQL Server not coming online. In case you’re also facing the same issue or planning to adopt new TLS standards like TLS 1.2 and don’t want to such problems then you should go through this post. Problem: We may require to make changes like enabling higher level cipher suite TLS 1.2 and disable TLS 1.0 for better security of our servers. But in case you make this changes it can be possible that after restart the SQL Services refuse to come online and fails. If you check the SQL Server error logs you’ll get the below errors: Error: 17182, Severity: 16, State: 1. TDSSNIClient initialization failed with error 0x139f, status code 0x80. Reason: Unable to initialize SSL sup
Read more

SQL Server 2014 SP\CU installation getting stuck at “MsiTimingAction”

By
Image
Hello folks, and welcome back to this new post on Sidd's SQL Solutions. Recently I was working on regular SQL Server patching stuff, which often we have to do and I faced this odd situation on one of the server, which troubled me a lot. Fortunately I was able to fix it after two days of struggle, which also gave me my new post. This blog post will cover this issue and its fix in details. The Issue: The odd situation or the issue I faced here was while installing S ervice P ack 2 for SQL Server 2014 on a server running with  Windows Server 2012 R2  OS. The setup starts up normally like others but gets stuck at “MsiTimingAction ” page and  simply refuses to progress any further . No matter how many times you restart the setup or your system\server it end up at the same stage. It seems to be a known issue, which was fixed in C umulative U pdate 2 of S ervice P ack 1 for SQL Server 2014. The system on which I was applying this patch was already running with CU8
Read more

SQL Server: Cluster Installation failed with error “Wait on the Database Engine recovery handle failed.”

By
Image
So, it is a nice fine day and you’re in office doing your BAU. You got this new task of installing SQL Cluster (which you’ve probably done several times before) and after making all necessary arrangements and planning you started it. All went fine and then when the setup is almost done you got this strange error “Wait on the Database Engine recovery handle failed.” which ruins the rest of your day. No worries we are here to sort this out quickly for you. Problem Now the problem here is that SQL Server setup is able to install all the components but is failing for DB engine (and its group). You get the error pop-up stating the below error: “Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.” It may also give some link, which unfortunately (may) not lead you to anywhere. And at the end your see crosses against DB engine and its dependent components. Let me first tell you that why you got this error. In every SQL
Read more