SQL Server login success but The database dbName is not accessible ObjectExplorer

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

If you are using Sql Management Studio  just start it as Administrator   Right click- Run as Administrator

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

This fixed it for me   Use  dbName  GO  EXEC sp change users login  Auto Fix   Manoj   null   Manojspassword  GO

User · Answer

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   lt    check  db owner  for user that you are experience the issue   PROBLEM SOLVED

User · Answer

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

User · Answer

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

[sql-server] SQL Server : login success but "The database [dbName] is not accessible. (ObjectExplorer)"

Examples related to sql-server

[sql-server] SQL Server : login success but "The database [dbName] is not accessible. (ObjectExplorer)"

Examples related to sql-server

Examples related to login