CODEWITHSUNDEEP
sqlsql-serversql-server-2008
greg
greg

Reputation: 1314

The database [dbName] is not accessible. (ObjectExplorer)

I have an issue in regards to using SQL Server 2008 R2.

I recently had an issue with my computer and therefore I had to reboot windows and had to grant permission from one user to another user (using the security feature in the properties). When giving permission initially though, it through a "Access Denied" message.

After much research, it stopped producing this error (the user which I needed to grant permission too wasn't available), which then caused another issue to occur, but this time within SQL Server. It produces this message;

The database [dbName] is not accessible. (ObjectExplorer)

This error occurs when I try to select the drop down option to see the list of tables and stored procedures of the database within SQL Server. I found an explanation for this on the following link;

http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=916

And I then tried to implement like so;

 USE msdb;
 GO
 GRANT CONNECT TO [DBName\MyName] ;

 CREATE DATABASE [DBNAME] ON PRIMARY

Using a script I created (luckily before this problem occurred) it through a whole lot of messages;

Msg 15151, Level 16, State 1, Line 1
Cannot find the user 'DBName\MyName', because it does not exist or you do not have permission.
Msg 262, Level 14, State 1, Line 2
CREATE DATABASE permission denied in database 'master'.
Msg 5011, Level 14, State 9, Line 1
User does not have permission to alter database 'DBName', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1 ...

After this bunch of errors, I have become unstuck and therefore would much be grateful if anyone could give me some feedback in regards to what I could do to resolve this issue. Cheers.

Upvotes: 15

Views: 112146

Answers (4)

Samson M. Adeyemo
Samson M. Adeyemo

Reputation: 51

Login with sa and expand Security > Login, right click on the username and then properties, Click User Mapping and select the DB you want the user to access and then Ok

Upvotes: 5

user3259669
user3259669

Reputation: 19

Seems the database you are trying to access do not have admin privileges. Close SSMS and open it by Run as Administrator.

Upvotes: 1

Yasmine GreenApple
Yasmine GreenApple

Reputation: 448

One possible problem is that you have two instances of the database, so you have to deactivate one instance, if you have your database opened and accessible in your server explorer in Visual studio close connexion then go to windows services and stop and restart SQL server service (MSSQL) then go to Management studio and open your database, it should be opened and you can explore tables from Management studio.

Upvotes: 1

Ben
Ben

Reputation: 35613

Generally it is a bad idea to grant permissions directly to logins. You should create role objects in the database, and all permissions in the database should be assigned to the roles.

Then when moving the database to another computer (or reinstalling) the only things you have to modify are server logins and role assignments.

Upvotes: 3

Related Questions