resolving permission conflicts

I have been trying to set up permissions for a SQL user ID
on a specific table. The user will access this table using
MS Access 2000 with a standard ODBC connection. I only
want the user to be able to view data from this table.
When I set up a user role in SQL associated with this
user, the permissions on this role are set to deny insert,
update and delete capabilities. This does not appear to
work. However, if I change the permissions on the public
role to deny insert, update, and delete capabilities this
works.
I know every user assumes the public role but I can't seem
to override the permissions set on the public role.
The SQL books online documentation states that a denied
permission always takes prcedent. I cannot seem to make
this work when I have a specific role for this user which
has the deny permissions set.
Thanks,
Jim d'HulstIs the user perhaps an admin on the server and thus inheriting sysadmin via
the Builtin\System role created by default? ( I normall remove this pretty q
uickly)
Alicia
www.sqlporn.co.uk|||Alicia, the user is jnot an admin on the server nor does
this SQL user id belong to any system administration roles.
What I find is if I use windows NT authentication and
assign the permissions the a windows users it works fine.
It is just when I use a SQL user ID that this doesn't seem
to work.
Thanks,
Jim
>--Original Message--
>Is the user perhaps an admin on the server and thus
inheriting sysadmin via the Builtin\System role created by
default? ( I normall remove this pretty quickly)
>Alicia
>www.sqlporn.co.uk
>.
>|||Jim,
Overriding public should be quite doable. Since it is working for your
domain account, I would expect it to work for a SQL account as well. With
all due deference, I suggest that it is probably something simple, but just
hard to see. Silly possibilities:
There is a guest account and the user name is misspelled such that the user
comes in as guest.
The user is also in the db_owner role for the database.
Russell Fields
"jim.dhulst@.am.dynonobel.com" <anonymous@.discussions.microsoft.com> wrote in
message news:fba601c43e7d$96ba1c70$a301280a@.phx.gbl...[vbcol=seagreen]
> Alicia, the user is jnot an admin on the server nor does
> this SQL user id belong to any system administration roles.
> What I find is if I use windows NT authentication and
> assign the permissions the a windows users it works fine.
> It is just when I use a SQL user ID that this doesn't seem
> to work.
> Thanks,
> Jim
> inheriting sysadmin via the Builtin\System role created by
> default? ( I normall remove this pretty quickly)