The database [dbName] is not accessible. (ObjectExplorer)
I have an issue in regards to using SQL Server 2008 R2.
I recently had an issue with my computer and therefore I had to reboot windows and had to grant permission from one user to another user (using the security feature in the properties). When giving permission initially though, it through a «Access Denied» message.
After much research, it stopped producing this error (the user which I needed to grant permission too wasn’t available), which then caused another issue to occur, but this time within SQL Server. It produces this message;
The database [dbName] is not accessible. (ObjectExplorer)
This error occurs when I try to select the drop down option to see the list of tables and stored procedures of the database within SQL Server. I found an explanation for this on the following link;
And I then tried to implement like so;
Using a script I created (luckily before this problem occurred) it through a whole lot of messages;
Msg 15151, Level 16, State 1, Line 1
Cannot find the user ‘DBName\MyName’, because it does not exist or you do not have permission.
Msg 262, Level 14, State 1, Line 2
CREATE DATABASE permission denied in database ‘master’.
Msg 5011, Level 14, State 9, Line 1
User does not have permission to alter database ‘DBName’, the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1 .
After this bunch of errors, I have become unstuck and therefore would much be grateful if anyone could give me some feedback in regards to what I could do to resolve this issue. Cheers.
Fix: The Database “” is Not Accessible (ObjectExplorer) – Microsoft SQL Server Management Studio
In Microsoft SQL Server Management Studio, you may connect to a database but receive the following error.
The database “” is not accessible. (ObjectExplorer)
The cause of this error is because you connected to a Microsoft database in Single User mode. Start the SQLServer instance with the –m parameter then start SQL Server Management Studio using windows authentication. Assign yourself the sysadmin role to your user then remove the the –m parameter. Restart SQL server.
SQL Server 2008: The database [dbName] is not accessible
I got this error from another person and I’ve run out of ideas. I can access this database without any problems, the database is in MULTI_USER mode, the person receiving the error is a user, etc.
The database is READ_ONLY and the user in question has previously used this database and only has database read permissions, so there’s no chance of settings being tampered with.
Yesterday, I added a few functions and had to set to SINGLE_USER and READ_WRITE, but I’ve triple checked that the db has been set back to MULTI_USER. I can’t decide if that’s coincidence or causality. Either way, I’m at a dead end.
Here’s the error report:
Any experience with this kind of error?
2 Answers 2
Typically this is caused when the user’s default database is set to a database they don’t have permissions in, or it’s offline.
For example, say I set you up a login in the Sales database, and then I right-click on your login in SSMS and change your default database to master — but you don’t have any permissions in Master. When you try to connect, even though you want the Sales database, you’ll go into Master by default, and get this error.
This can also happen if someone takes away your permissions in Sales and doesn’t change your default database to something else.
This is why I love setting all users’ default databases to TempDB. User databases can come and go, but TempDB will always be there. Plus if somebody accidentally creates an object, at least it’s in a throwaway database instead of something like Master or Sales.
Tutorials, tips&tricks, snippets..
My database was in Single User mode. I don’t know how it entered in this mode, but this causes a lot of problem with the database itself. For example, trying to expand the database, you get an error:
The database ‘my_db’ is not accessible.(ObjectExplorer)
Also, when trying to delete it, you get:
Changes to the state or options of database ‘my_db’ cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
How to fix this?
SSMS in general uses several connections to the database behind the scenes. You will need to kill these connections before changing the access mode.
-
- First, make sure the object explorer is pointed to a system database like master.
- Second, execute a sp_who2 and find all the connections to database ‘my_db’. Kill all the connections by doing KILL where session id is the SPID listed by sp_who2. Here for more info about sp_who2.
- Third, open a new query window.Execute the following code.
NOTE: if you have some problems killing the connections, you can simply restart the service SQL Server
. Once this procedure is done, it can happen the database enters in the Recovery Pending Status. Just click here to read how to fix this.