SQL Server Login Failed – Cannot Open Database Requested by the Login

permissionssql serversql-server-2012

I have a SharePoint environment, but my problem is related to DB as I couldn't solve it in SharePoint ways.

I have a database that suddenly stopped working. It's appearing in SQL management studio, but without the + sign. If I right click on it to see the tasks, they're disabled.

I have full control over the server as am the one who installed SQL and Windows. Any idea how to solve this.

Best Answer

sp_readerrorlog

As pointed out already by @Shanky read the error log (ERRORLOG) by setting off the sp_readerrorlog command:

sp_readerrorlog 0,1,'<your_DB>'

You could also use the official xp_readerrorlog procedure with the same parameters:

xp_readerrorlog 0,1,'<your_DB>'

xp_readerrorlog is the underlying extended stored procedure for the sp_readerrorlog stored procedure which is explained here: SQL Internals : Useful Parameters for xp_readerrorlog

Read ERRORLOG directly

If you are unable to read the ERRORLOG with the stored procedures, then manually read the ERRORLOG files (ERRORLOG.*) to find any errors.

Querying Registry to find location

If you can't find the location of the ERRORLOG file, then retrieve the path from the registry: 

DECLARE @regout VARCHAR(255)  
exec xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer\Parameters', 'SQLArg1', @regout OUTPUT  
SELECT @regout

Don't forget to replace MSSQLSERVER with your instance name if you are using a different instance than the default instance (...\MSSQL11.MSSQLSERVER\...).

Query SQL Server directly

SQL Server itself stores some basic information about the status of the databases in sys.databases:

select name, state_desc from sys.databases

You should get an output similar to the following:

name    state_desc
master  ONLINE
tempdb  ONLINE
model   ONLINE
msdb    ONLINE
<your_DB>   ONLINE

Permissions

If your database <your_DB> is online, then you possibly don't have adequate permissions to read the database. Change your user's permissions.

Once you have found more information you will possibly be able to pinpoint the issue.

Related Question