473,326 Members | 2,732 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

SQL Server 7 ignores user permissions

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??
Jul 20 '05 #1
3 2536

"Jon Ley" <no******@nospam.com> wrote in message
news:3f**********************@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
Jul 20 '05 #2

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:3f**********@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.
Jul 20 '05 #3
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" <sq*@hayes.ch> wrote in message
news:3f**********@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

Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Brad H McCollum | last post by:
I'm writing an application using VB 6.0 as the front-end GUI, and the MSDE version of SQL Server as the back-end (it's a program for a really small # of users --- less then 3-4). I'm trying to...
2
by: Tina Robichaux | last post by:
I have found info on this problem at MS, but they say this problem does not occur with SQL 2K and MDAC 2.6 SP2, yet I am still experiencing it: I have a SQL user specifically created to SELECT...
2
by: Tina Robichaux | last post by:
I have found info on this problem at MS, but they say this problem does not occur with SQL 2K and MDAC 2.6 SP2, yet I am still experiencing it: I have a SQL user specifically created to SELECT...
17
by: Jon B | last post by:
Hi All! I have a ASP.NET 2.0 site that works on the Windows 2000 Server. However, when I tried to view this site on my local Windows XP machine, I get "Server Unavailable". If I switch the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.