SQL Server : login success but "The database [dbName] is not accessible. (ObjectExplorer)"

For this situation you have to connect to database in Single-User mode.

Starting SQL Server in single-user mode enables any member of the computer's local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role.

Here you can find step-by-step instruction to do this.

In short you must start the sqlserver instance with parameters -m, after start Sql Server Management Studio with windows authentication.

Now you are a sysadmin, assign the sysadmin role to your user, exit and remove the -m parameter and restart sql server.

The problem is that the user in the database is an "orphan". This means that there is no login id or password associated with the user. This is true even if there is a login id that matches the user, since there is a GUID (called a SID in Microsoft-speak) that has to match as well.

This used to be a pain to fix, but currently (SQL Server 2000, SP3) there is a stored procedure that does the heavy lifting.

All of these instructions should be done as a database admin, with the restored database selected.

First, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

this text was obtained at http://www.fileformat.info/tip/microsoft/sql_orphan_user.htm in Dez-13-2017

Really stupid solution but I'll add it here in case anyone gets here from a Google search.

I'd just restarted the SQL service and was getting this error and in my case, just waiting 10 minutes was enough and it was fine again. Seems this is the error you get when it is just starting up.