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

Solution 1:

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.

Solution 2:

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