patternModerate
SQL Server 2008: The database [dbName] is not accessible
Viewed 0 times
dbnamethe2008sqlaccessibledatabaseservernot
Problem
I got this error from another person and I've run out of ideas. I can access this database without any problems, the database is in MULTI_USER mode, the person receiving the error is a user, etc.
The database is READ_ONLY and the user in question has previously used this database and only has database read permissions, so there's no chance of settings being tampered with.
Yesterday, I added a few functions and had to set to SINGLE_USER and READ_WRITE, but I've triple checked that the db has been set back to MULTI_USER. I can't decide if that's coincidence or causality. Either way, I'm at a dead end.
Here's the error report:
Any experience with this kind of error?
The database is READ_ONLY and the user in question has previously used this database and only has database read permissions, so there's no chance of settings being tampered with.
Yesterday, I added a few functions and had to set to SINGLE_USER and READ_WRITE, but I've triple checked that the db has been set back to MULTI_USER. I can't decide if that's coincidence or causality. Either way, I'm at a dead end.
Here's the error report:
The database [dbName] is not accessible. (Object Explorer)
----------------------------------------
Program Location:
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.DatabaseNavigableItem.get_CanGetChildren()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.RequestChildren(IGetChildrenRequest Request)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ExplorerHierarchyNode.BuildChildren(WaitHandle quitEvent)Any experience with this kind of error?
Solution
Typically this is caused when the user's default database is set to a database they don't have permissions in, or it's offline.
For example, say I set you up a login in the Sales database, and then I right-click on your login in SSMS and change your default database to master - but you don't have any permissions in Master. When you try to connect, even though you want the Sales database, you'll go into Master by default, and get this error.
This can also happen if someone takes away your permissions in Sales and doesn't change your default database to something else.
This is why I love setting all users' default databases to TempDB. User databases can come and go, but TempDB will always be there. Plus if somebody accidentally creates an object, at least it's in a throwaway database instead of something like Master or Sales.
For example, say I set you up a login in the Sales database, and then I right-click on your login in SSMS and change your default database to master - but you don't have any permissions in Master. When you try to connect, even though you want the Sales database, you'll go into Master by default, and get this error.
This can also happen if someone takes away your permissions in Sales and doesn't change your default database to something else.
This is why I love setting all users' default databases to TempDB. User databases can come and go, but TempDB will always be there. Plus if somebody accidentally creates an object, at least it's in a throwaway database instead of something like Master or Sales.
Context
StackExchange Database Administrators Q#21817, answer score: 10
Revisions (0)
No revisions yet.