I am setting up a web application, where the application needs to access a
database. I have created the user (for 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.
(ObjectExplorer)"

How do I make a database accessible to a login, using Server Management
Studio?

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 - don't 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.

"jrl" <jrl@newsgroup.nospam> wrote in message
news:LvidncDJLee6jZzVnZ2dnUVZ_jWdnZ2d@novus-tele.net...
>I am setting up a web application, where the application needs to access a
>database. I have created the user (for 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. (ObjectExplorer)"
>
> How do I make a database accessible to a login, using Server Management
> Studio?

You should map your Login to a User in your database and grant some
permissions to that user to make him able to write\read data from that
database.

To do this in SSMS (as you asked for this) go to Security node from the
Object Explorer and find your Login in the Logins node. Go to its properties
and then go to User Mapping. You'll see your databases on the list, choose
the database that you want to create a User for your Login to map. And then
you can add your user to some roles using the below list in the same window.

Another way to give granular permissions to your database user is to go to
database properties by expanding the Databases node in the Object Explorer
in SSMS and on the left pane list choosing the Permissions and after
choosing your database user on the list assigning necessary permissions to
him\her. In this case, you do not have to add this User to a role or
something...

--
Ekrem Önsoy



"jrl" <jrl@newsgroup.nospam> wrote in message
news:LvidncDJLee6jZzVnZ2dnUVZ_jWdnZ2d@novus-tele.net...
>I am setting up a web application, where the application needs to access a
>database. I have created the user (for 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. (ObjectExplorer)"
>
> How do I make a database accessible to a login, using Server Management
> Studio?

Thanks for the response. I tried your suggestions, but they didn't get me
access to the explicit permisions. However, I was able to combine your steps
with mine, and now it works. It just amazes me how SQL seems to have been
made overly complicated.

Anyway, for learning sake, here were my results of your directions:

First off, I started with a default installation of SQL 2005 SP2.

Following suggestion 1
1) I created a login (view) .
2) server\store>security>logins I selected properties of view, and user
mapping.
3) Here I see that database monitor, has user webapp, and the default schema
is dbo. If I browse for objects, I see a list of permissions, but not at the
level of insert, delete, update, select. Rather at the level of
db_accessadin, db_datareader, and so on. I'm not sure if these are the same
as insert, delete, update, select.

Following suggestion 2
7) At server\store>databases>monitor>security>Users I create a new database
user (user name is webapp). For the login name entry, I browse for object,
select view.
8) At server\store\security\Users\ under the general property I would need
to specify a schema or role.
9) End of part two: how do I select the necessary permissions: insert,
delete, update, select.


To correct this I integrated some of my previous procedure:
1) For each database go to security>Roles>Database Roles and create a new
role (with out assigning a schema).
2) Under database properties, select permissions in left panel, (here I see
the username Webapp is visible) but I add the role, minimal. Then I select
the role minimal, and grant explicit permission update, select, insert,
delete.
3) Select the user (for each database), properties. Under database role
membership, select minimal.

Now I'm able to log in as view (even though this is a role, not a user!) and
it can access the databases with the permissions: insert, delete, update,
select.

I sure wish I could find a good tutorial to explain the structures that are
being used here. If you have any comments on what I did, or why I couldn't
see permissions: insert, delete, update, select when I used your method, I'd
be glad to learn.



"Ekrem Önsoy" <ekrem@compecta.com> wrote in message
news:BC875A00-314C-417F-AB02-1B1037B8C96A@microsoft.com...
> You should map your Login to a User in your database and grant some
> permissions to that user to make him able to write\read data from that
> database.
>
> To do this in SSMS (as you asked for this) go to Security node from the
> Object Explorer and find your Login in the Logins node. Go to its
> properties and then go to User Mapping. You'll see your databases on the
> list, choose the database that you want to create a User for your Login to
> map. And then you can add your user to some roles using the below list in
> the same window.
>
> Another way to give granular permissions to your database user is to go to
> database properties by expanding the Databases node in the Object Explorer
> in SSMS and on the left pane list choosing the Permissions and after
> choosing your database user on the list assigning necessary permissions to
> him\her. In this case, you do not have to add this User to a role or
> something...
>
> --
> Ekrem Önsoy
>
>
>
> "jrl" <jrl@newsgroup.nospam> wrote in message
> news:LvidncDJLee6jZzVnZ2dnUVZ_jWdnZ2d@novus-tele.net...
>>I am setting up a web application, where the application needs to access a
>>database. I have created the user (for 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. (ObjectExplorer)"
>>
>> How do I make a database accessible to a login, using Server Management
>> Studio?
>

I believe that if you take a look at the following article and read it till
its end, it won't seem complicated anymore. Although this article is about
SQL Server 2000, you'll see useful information in it. As far as I know,
permissions of the roles are the same for 2000/2005.
http://www.sqlservercentral.com/arti...aseroles/1231/

First, you better understand the fixed database and server roles in SQL
Server.

And then you must know that you can assign granular permissions using
Permissons pane in the properties of your databases as I told you before. In
this case, you don't have to use fixed roles. You can explicitly assign
permissions to your database users using this way.

Things we don't know usually looks scary and complicated, but after shedding
a light on them, we see things clearer.

--
Ekrem Önsoy



"jrl" <jrl@newsgroup.nospam> wrote in message
news:J-WdnfA22e7e7JzVnZ2dnUVZ_uidnZ2d@novus-tele.net...
> Thanks for the response. I tried your suggestions, but they didn't get me
> access to the explicit permisions. However, I was able to combine your
> steps with mine, and now it works. It just amazes me how SQL seems to have
> been made overly complicated.
>
> Anyway, for learning sake, here were my results of your directions:
>
> First off, I started with a default installation of SQL 2005 SP2.
>
> Following suggestion 1
> 1) I created a login (view) .
> 2) server\store>security>logins I selected properties of view, and user
> mapping.
> 3) Here I see that database monitor, has user webapp, and the default
> schema is dbo. If I browse for objects, I see a list of permissions, but
> not at the level of insert, delete, update, select. Rather at the level of
> db_accessadin, db_datareader, and so on. I'm not sure if these are the
> same as insert, delete, update, select.
>
> Following suggestion 2
> 7) At server\store>databases>monitor>security>Users I create a new
> database user (user name is webapp). For the login name entry, I browse
> for object, select view.
> 8) At server\store\security\Users\ under the general property I would
> need to specify a schema or role.
> 9) End of part two: how do I select the necessary permissions: insert,
> delete, update, select.
>
>
> To correct this I integrated some of my previous procedure:
> 1) For each database go to security>Roles>Database Roles and create a new
> role (with out assigning a schema).
> 2) Under database properties, select permissions in left panel, (here I
> see the username Webapp is visible) but I add the role, minimal. Then I
> select the role minimal, and grant explicit permission update, select,
> insert, delete.
> 3) Select the user (for each database), properties. Under database role
> membership, select minimal.
>
> Now I'm able to log in as view (even though this is a role, not a user!)
> and it can access the databases with the permissions: insert, delete,
> update, select.
>
> I sure wish I could find a good tutorial to explain the structures that
> are being used here. If you have any comments on what I did, or why I
> couldn't see permissions: insert, delete, update, select when I used your
> method, I'd be glad to learn.
>
>
>
> "Ekrem Önsoy" <ekrem@compecta.com> wrote in message
> news:BC875A00-314C-417F-AB02-1B1037B8C96A@microsoft.com...
>> You should map your Login to a User in your database and grant some
>> permissions to that user to make him able to write\read data from that
>> database.
>>
>> To do this in SSMS (as you asked for this) go to Security node from the
>> Object Explorer and find your Login in the Logins node. Go to its
>> properties and then go to User Mapping. You'll see your databases on the
>> list, choose the database that you want to create a User for your Login
>> to map. And then you can add your user to some roles using the below list
>> in the same window.
>>
>> Another way to give granular permissions to your database user is to go
>> to database properties by expanding the Databases node in the Object
>> Explorer in SSMS and on the left pane list choosing the Permissions and
>> after choosing your database user on the list assigning necessary
>> permissions to him\her. In this case, you do not have to add this User to
>> a role or something...
>>
>> --
>> Ekrem Önsoy
>>
>>
>>
>> "jrl" <jrl@newsgroup.nospam> wrote in message
>> news:LvidncDJLee6jZzVnZ2dnUVZ_jWdnZ2d@novus-tele.net...
>>>I am setting up a web application, where the application needs to access
>>>a database. I have created the user (for 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. (ObjectExplorer)"
>>>
>>> How do I make a database accessible to a login, using Server Management
>>> Studio?
>>
>