SQL Server : login success but "The database [dbName] 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

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.

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.

Received error.

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.

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.

If you are using Sql Management Studio, just start it as Administrator.

Right click->Run as Administrator

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

I experienced a similar problem after running a few jobs of bulk insert through a Python script on a separate machine and a separate user from the one I am logging in to SSMS.

It appears that if the Python kernel (or possibly any other connection) is interrupted in the middle of a bulk insert job without properly 'cleaning up' the mess, some sort of hanging related to user credentials and locks may happen on the SQL Server side. Neither restarting the service nor the whole machine worked for me.

The solution in my case was to take the DB offline and online. In the SQL Server Management Studio, that is a right click on DB > tasks > take offline and then right click on DB > tasks > bring online.

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.

Get this error in this steps:

1. Run "Get offline".
2. "Get offline" was running too long, so i closed this window.
3. Then i got this error.

Steps to fix:

1. Go to "Activity monitor" and delete all connections to this db. Then DB became really offline and all is ok.

In my case, restarting SQL Server Service was enough to resolve the issue.

My issue got resolved by restarting the MS SQL server service, simple.

In my case it worked when I had opened SQL Server Management Studio with Administrator credentials and I right-clicked on the database and select "Go online" or something like this.

I had a similar problem, for me I had to create a new user with name that I needed, in your case you should create some like this:

USE [master]

GO

/****** Object:  Login [Manoj_2]    Script Date: 9/5/2019 12:16:14 PM ******/
CREATE LOGIN [Manoj_2] FROM WINDOWS WITH DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english]

GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [Manoj_2]
GO

Execute the following sentence:

EXEC rdsadmin.dbo.rds_set_database_online dbname

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

I just restarted my SQL Server (MSSQLSERVER) with which my SQL Server Agent (MSSQLSERVER) also got restarted. Now am able to access the SQL SERVER 2008 R2 database instance through SSMS with my login.

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

Go to

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.

PROBLEM SOLVED...