SQL Server 7.0 ignores user permissions

From: Jon Ley (no.email_at_nospam.com)
Date: 10/27/03 

Date: Mon, 27 Oct 2003 12:23:25 -0000

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??

Relevant Pages

  • ASP.NET Forms Authentication Best Practices
    ... ASP.NET Forms Authentication Best Practices ... What happens if your user database is compromised? ... Listing One, where you want to use login.aspx to log users in. ... string FirstName ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Security question ..
    ... What I want to prevent is any access to the database accept through our ... application unless you have elevated permissions. ... Authentication, if he is smart enough to create an NT Auth ODBC connection ... passes through to the database or to use SQL Server authentication. ...
    (microsoft.public.sqlserver.server)
  • User authentication over the web (was: Secure Password in database)
    ... Subject: User authentication over the web (was: Secure Password in database) ... a web server is usually authenticated to users by using SSL or TLS ...
    (SecProg)
  • Re: Using SSPI with Custom Authentication
    ... It boils down to moving the security perimeter away from the server. ... first database contains all of the user information; ... perform authentication, and users need to be authenticated before accessing ...
    (microsoft.public.dotnet.security)
  • Re: SQL Express MDF file moved from 1 computer to another - auth f
    ... I try to attach the database which is located in MyDocuments\Visual ... Microsoft SQL Server Management Studio ... now it tells me that authentication has failed for my windows ...
    (microsoft.public.sqlserver.connect)