[FIX>SQL Server Error “The database XXXX is not accessible (ObjectExplorer)”
Recently after restoring a database backup which i got from another server i repeatedly encountered an error when logged in using a user who is given permission. But when ‘sa’ account is used i could work with the database without any problem.
Later i found that the issue is because the user in the restored database is not properly mapped to the user in the new server and the resolution for this is to run the stored procedure sp_change_users_login to correct the orphaned user.
- sp_change_users_login ‘update_one’, ‘USER’, ‘LOGIN’ — Links the given user in the current database to the specified login.
sp_change_users_login ‘suto_fix’, ‘USER’ –Links the given user in the current database to the login having the same name in the current server.
After running this you will be able to access the restored database without any issue using the mentioned database user.
The database dbname is not accessible
Actually, I wouldn’t write an article for this error, but when I saw that it was the most searched article for another and a similar error, I wanted to write it.
You may receive this error when you try to open one of the databases under Databases in the Object Explorer window in SQL Server Management Studio. The probable cause is that you do not have access to the database you are trying to open.
SOLUTION:
You must request authorization for your SQL Login to access the relevant database. In most cases, only access is not sufficient. You will also need authorizations such as read (db_datareader) and write (db_datawriter).
Of course you need to determine the authority you need, I just made a very general description.
In the meantime, let me tell you what I mean by the access authorization. I’m talking about having a user in the relevant database. You can create it as follows:
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.
The database is not accessible (ObjectExplorer) : Error Resolved
If you can access other databased from Sql Server Management Studio and have issue with one or two databases and getting error of The database is not accessible (ObjectExplorer) try out following steps
Caution :
This is generally happens when you restore database from other server. It may happen you have just restored the database of that server not specific user and trying to access that database.
or
This can be caused when you have removed your default database of particular user and you are trying to login with that credentials.
Solution :
1. You are logged in with the user who has permission to access that database. Try to login with sa user or Windows Authenticated User.
2. If database is not accessible even with sa or Windows Login User try to bring it offline database and then bring it online and then check.
3. Last Option you can deattach the database and attach it again. (It may be possible data loss here – 99% it won’t be). Just be careful while doing this.