> We also placed an 'X' in
> all columns for guest and public account.

The red 'X' denotes an explicit DENY. Because DENY takes precedence over a
GRANT, the effective result is that no users can access the view because all
users are members of the public role. It is generally best to avoid using
DENY unless you have a specific reason to use it.

> Should I create roles, then add users to the roles (so I can remove
> public). This seems a little different than the NT model of
> users/groups.

Yes, it's a good practice to grant permissions to roles rather than
individual users. This allows you to more easily control data access via
role membership. You can ignore the public role unless you want to grant
permissions to all database users.

I suggest you clear (REVOKE) all permissions on the view and then grant
permissions only to those roles as desired.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Noloader" <noloader@yahoo.com> wrote in message
news:6b543aa7.0404291445.4c89401b@posting.google.c om...
> Hello,
>
> We are using Access Front End (ADP Project) and SQL Server 2000
> backend. SQL Server is using NT Security.
>
> We do not want the users to access the the underlying tables. So, we
> went to each table and unchecked 'Select'. We also placed an 'X' in
> all columns for guest and public account.
>
> Next, we created a view. The view has 'Select' permission given to the
> users (it is also enforcing some business rules on whether a record
> should be returned).
>
> Access XP is behaving as expected - the tables are not showing up, and
> a report can be created using the view.
>
> Here is the test situation we have set up:
> User 'test_a' has 'select' allowed on the view.
> Built in user 'public' and 'guest' were not allowed on the view.
>
> When we configured the view as stated above, 'test_a' did not have
> access to the view (even though we specifically granted). It appears
> the 'public' must also have at least 'Select'.
>
> It is not the case for a member of BUILTIN\Administrators.
>
> Is this expected behavior? Does SQL server not follow the NT Security
> model? Or am I simply missing something with the 'public' role?
>
> Should I create roles, then add users to the roles (so I can remove
> public). This seems a little different than the NT model of
> users/groups.
>
> Also, thanks to Simon and Pete for their previous replies.
>
> Jeff
> noloader@yahoo.com