I am having a problem restricting write access to tables in my database.

In my database I have a table called, for the sake of argument, 'TableX'.

In my SQL Server Logins, I have set up a login for 'Domain Users' using NT
authentication, and a login called 'FullTableX', using SQL Server
authentication.

I have added two users to my database relating to the above logins.

I have added a role to my database called 'ReadTableX' with 'Domain Users'
as a member of this role. 'ReadTableX' has SELECT permission only on a
restricted set of tables.

The only other role that 'Domain Users' is a member of is 'public', and
'public' has no permissions on any of my tables.

The user 'FullTableX' is a member of 'public', 'db_datareader' and
'db_datawriter'

With the above settings, I would expect user 'FullTableX' to have full
access (Select, Insert, Update and Delete) on all my database tables (so far
so good), but any user connecting to the database with NT authentication
(via ODBC System DSN) should only have read access to the limited set of
tables. However, what is happening is that NT authenticated users also have
full access to all of the tables.

What am I missing here??

"Jon Ley" <no.email@nospam.com> wrote in message
news:3f86c93a$0$8762$ed9e5944@reading.news.pipex.n et...
> I am having a problem restricting write access to tables in my database.
>
> In my database I have a table called, for the sake of argument, 'TableX'.
>
> In my SQL Server Logins, I have set up a login for 'Domain Users' using NT
> authentication, and a login called 'FullTableX', using SQL Server
> authentication.
>
> I have added two users to my database relating to the above logins.
>
> I have added a role to my database called 'ReadTableX' with 'Domain Users'
> as a member of this role. 'ReadTableX' has SELECT permission only on a
> restricted set of tables.
>
> The only other role that 'Domain Users' is a member of is 'public', and
> 'public' has no permissions on any of my tables.
>
> The user 'FullTableX' is a member of 'public', 'db_datareader' and
> 'db_datawriter'
>
> With the above settings, I would expect user 'FullTableX' to have full
> access (Select, Insert, Update and Delete) on all my database tables (so
far
> so good), but any user connecting to the database with NT authentication
> (via ODBC System DSN) should only have read access to the limited set of
> tables. However, what is happening is that NT authenticated users also
have
> full access to all of the tables.
>
> What am I missing here??
>
>

One possibility is that Domain Users is in the local Administrators group on
the server (BUILTIN\Administrators) - that group is in the sysadmin role by
default. Or is there another NT group, either local or domain, which is a
member of sysadmin or db_owner, and has Domain Users in it?

Simon

"Simon Hayes" <sql@hayes.ch> wrote in message
news:3f86dfe0$1_1@news.bluewin.ch...
>
> One possibility is that Domain Users is in the local Administrators group
on
> the server (BUILTIN\Administrators) - that group is in the sysadmin role
by
> default. Or is there another NT group, either local or domain, which is a
> member of sysadmin or db_owner, and has Domain Users in it?
>

Simon,

Domain Users is not in the local Administrators group, and the only other NT
group that has access does not have Domain Users as a member of it.

I have also tried playing around with a test database, where I have
explicitly denied permissions on all the tables for public, and set roles of
public, db_denydatareader and db_denydatawriter for Domain Users. This is
also being ignored, and going in with NT security allows me full access on
all the tables. There must be something really simple that I'm overlooking
here, but it's got me completely stumped.

Any other ideas?

Jon.

Simon,

You were right, I've finally got it sussed. It turns out that I am a members
of Domain Admins, which in turn is automatically added to the local
administrators group on each server. So I guess this is what's giving me
write access to the tables. I have confirmed that a standard user does _not_
have write access, so I'm happy now!

Jon.

"Simon Hayes" <sql@hayes.ch> wrote in message
news:3f86dfe0$1_1@news.bluewin.ch...
>
> One possibility is that Domain Users is in the local Administrators group
on
> the server (BUILTIN\Administrators) - that group is in the sysadmin role
by
> default. Or is there another NT group, either local or domain, which is a
> member of sysadmin or db_owner, and has Domain Users in it?
>
> Simon
>
>