TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server principal "abc" is not able to access the database "userdatabase" under the current security context. (Microsoft SQL Server, Error: 916)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=916&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Problem
=======
I have a login which has access to one of the databases on the SQL Server instance but not all, when the user connects to SQL Server remotely and tries to expand databases it gets the message that it does not has access to one of the user databases (on which it actually should not have access) and in the process user is also unable to access his/her own database as they can't get past the error
=======
I have a login which has access to one of the databases on the SQL Server instance but not all, when the user connects to SQL Server remotely and tries to expand databases it gets the message that it does not has access to one of the user databases (on which it actually should not have access) and in the process user is also unable to access his/her own database as they can't get past the error
Error:
=======
=======
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server principal "abc" is not able to access the database "userdatabase" under the current security context. (Microsoft SQL Server, Error: 916)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=916&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Reason
========One of the databases to which the user did not had access to had autoclose on, when we try and expand databases in SQL Server Management Studio, it tries to at least start all the databases and since the user abc in my case did not had access to the the database "userdatabase" he was getting an error. A quick analysis of trace file collected by Profiler will show you something like below:
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server principal "abc" is not able to access the database "userdatabase" under the current security context. (Microsoft SQL Server, Error: 916)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=916&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Reason
========One of the databases to which the user did not had access to had autoclose on, when we try and expand databases in SQL Server Management Studio, it tries to at least start all the databases and since the user abc in my case did not had access to the the database "userdatabase" he was getting an error. A quick analysis of trace file collected by Profiler will show you something like below:
2011-03-08 11:55:32.20 spid67 Starting up database 'Userdatabase'.
Followed by the error
Solution:
===========
Turn off Autoclose on the database userdatabase and you should be able to connect now
More Information
===============
If Autoclose is set to true, the every time you would try and connect to the SQL Server it would attempt to start the database which may cause issues if you don't have access to the database. SQL Server need to start the database because it needs to collect basic information about the database before it can show it under the databases TAB in SSMS.
--Abhishek Soni
