SQL Server : login success but “The database is not accessible. (ObjectExplorer)”
I am using windows 8.1 and SQL Server 2012.
I was using an OS account “Manoj” for accessing SQL SERVER with windows authentication.
Recently I have deleted my user account “Manoj” of OS and created a new account with same name “Manoj”.
But the system took the new account as “Manoj_2”. This change keeps me out from accessing the old databases, I have created.
It says that
The database [dbName] is not accessible. (ObjectExplorer)
whenever I try to access any of the previous DBs I have created.
I used to create new login in SQL Server for “Manoj_2”, with default DB as “master”. But still the problem persists.
I cannot able to detach the DBs. I am unable to expand the DBs.
Note: In OS, I have admin rights for the “Manoj” account.
Please anybody tell me, what to do? either with OS or with SQL Server
12 Solutions collect form web for “SQL Server : login success but “The database 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.
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.
This is caused when the user's default database is set to a database they don't have permissions or its offline.
Just try to re add the user.Pleae have a look here too.
Security >> Logins >>
Right click to the user >> Properties >>
On the left navigation move to >> User Mapping >> Check the database and in the “Database role membership for: <>” check “db_owner” for user that you are experience the issue.
This is what led me to this issue and how I fixed it:
Restored my database to another SQL server instance from a .bak file, which included a preexisting user.
Tried to access the restored database from my app as usual using the same connection string but updated server instance.
Deleted user as the DBowner, then readded with exact same credentials, mappings, login, etc.
Was able to login as the user after readding the user after the restore.
Please try this script.. What this script does is it looks at the active sessions of the database and kills them so you can bring the database back online.
CREATE TABLE #temp_sp_who2 ( SPID INT, Status VARCHAR(1000) NULL, Login SYSNAME NULL, HostName SYSNAME NULL, BlkBy SYSNAME NULL, DBName SYSNAME NULL, Command VARCHAR(1000) NULL, CPUTime INT NULL, DiskIO INT NULL, LastBatch VARCHAR(1000) NULL, ProgramName VARCHAR(1000) NULL, SPID2 INT , rEQUESTID INT NULL --comment out for SQL 2000 databases ) INSERT INTO #temp_sp_who2 EXEC sp_who2 declare @kill nvarchar(max)= '' SELECT @kill = @kill+ 'kill '+convert(varchar,spid) +';' FROM #temp_sp_who2 WHERE DBName = 'databasename' exec sp_executesql @kill ALTER DATABASE DATABASENAME SET ONLINE WITH IMMEDIATE ROLLBACK
In my case, I simply had to start the application with “Run as administrator” in order to access anything. Otherwise I’d get the error you mentioned.
I had twoo users: one that had the sysadmin role, the other one (the problematic one) didn’t.
So I logged in with the other user(you can create a new one) and checked the ckeck box ‘sysadmin’ from: Security –> Logins –> Right ckick on your SQL user name –> Properties –> Server Roles –> make sure that the ‘sysadmin’ checkbox has the check mark.
Press OK and try connecting with the newly checked user.
This fixed it for me:
Use [dbName] GO EXEC sp_change_users_login 'Auto_Fix','Manoj', null, 'Manojspassword' GO
Issue: The database [dbName] is not accessible. (ObjectExplorer) got the error when expanding the database.
Solution: Deattach the database > Drop Option
Attach the database again with the mdf file under the mssql data folder
I performed the below steps and it worked for me:
1) connect to SQL Server->Security->logins->search for the particular user->Properties->server Roles-> enable “sys admin” check box
Get this error in this steps:
- Run “Get offline”.
- “Get offline” was running too long, so i closed this window.
- Then i got this error.
Steps to fix:
- Go to “Activity monitor” and delete all connections to this db. Then DB became really offline and all is ok.