Avatar of JeReLo
 asked on

how to make SQL database accessible to user?

I am setting up a web application, where the application needs to access a
database. I have created the user (which is the application) and it can log in to
the SQL 2005 Sp2 server, but when I try to open the required database using
Server Management Studio, it says "The database [name] is not accessible.

How do I make a database accessible to a login, using Server Management
Microsoft SQL Server 2005

Avatar of undefined
Last Comment

8/22/2022 - Mon
Daniel Reynolds

What is the database name?
Can you see the database in the mgr studio underneath server\databases\ ?

The name is monitor, and yes I can see monitor under the server\databases path.

Here is some specific background on how I got into this problem. I used a special setup for a web application (under Visual Studio 2008, Vista Ultimate, SQL 2005 SP2). I wanted the application to use minimal privileges, so I set up a user called view which was to use a role called minimal. The minimal role can just insert, delete, update, and select.  I had used a procedure (which I supply below) and this was previously working properly. However, when repeating it on a new server setup, I ran  into a slight difference, and the following errors ensued.
Previously I had used this sequence for configuring SQL to allow user view minimal access.
1)      For server\store I go to logins and create two users: ( "NT AUTHORITY\NETWORK SERVICE" and "view")
2)       For each database , go to security>Roles>Database Roles and ensure the role minimal exists. If not, create the role. (Select the database>security>roles>database roles, right click and create role minimal (just by adding  dont select any schema). Then select the database, aspnetdb, right click for permissions, add (select minimal), check permissions as Select, Insert, Update and Delete.
3)      Ensure that the user properties, within the monitor database, for view, shows the database role membership as minimal. If not, go to the top level security node, logins, user accounts, properties of user, select monitor, tick public and minimal in lower, and ok.

When I set this up a second time, everything was as above, except that I could not see the role public listed in step 3. I carried on.

If I log in as a windows authenticated user, I can see the database monitor.
If I set the default database for view to be monitor, then when I log in as view,  I get the error Cannot connect to [my database] Cannot open user default database. Login failed. Error 4064. 
If I set the default database for view to be master, then when I log in as view, then I can login. However when I try to access the database monitor, I get the error The database monitor is not accessible. (ObjectExplorer) 

I think somehow, maybe do I need to connect public in for step 3 above, or is there a further step for the login view, that would make monitor accessible.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Daniel Reynolds

It appears that the user "view" doesn't have enough priviledges to connect to the database.
You may want to give user view datareader privs on the database and see if that works.

Once there, continue taking rights away until you get where you want.

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.