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

Question

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.

Answer 1

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

Answer 2

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.