[sql-server] 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

This fixed it for me:

Use [dbName]
GO

EXEC sp_change_users_login 'Auto_Fix','Manoj', null, 'Manojspassword'
GO

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.

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...

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

Right click->Run as Administrator

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

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.

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.

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

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

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.

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.

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

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.

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.

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 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

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.

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

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.