Re: SQL Server 7.0 ignores user permissions

From: Dejan Sarka (dejan_please_reply_to_newsgroups.sarka_at_avtenta.si)
Date: 10/27/03 

Date: Mon, 27 Oct 2003 14:23:38 +0100

Is Windows user a member of some Windows groups? It can get the permissions
through Windows groups as well. For example, after the installation, the
local Administrators group is added to the sysadmin role, so the members
have all permissions in SQL Server. 

-- 
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Jon Ley" <no.email@nospam.com> wrote in message
news:3f9d0e71$0$9467$ed9e5944@reading.news.pipex.net...
> 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

  • Re: Removing Users from Database
    ... If the user is a member of any ... >group-level permissions in the database, ... >double-check the server login and verify that the login ...
    (microsoft.public.sqlserver.security)
  • Re: Alter Many Stored Procedures
    ... permissions to create/drop/alter other objects as well. ... a member of the db_ddladmin fixed database role ... SQL Server MVP ...
    (microsoft.public.sqlserver.security)
  • Re: Roles in Roles
    ... For a database user it is easy to find the database roles the user is a ... role is a member of. ... On SQL 2005 permissions is more intricate than ever, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Re: getting a list of groups a specific user belongs to in sql server 2000
    ... where login permissions were dictated by the users ... > The mapped login name should provide you with the groups being used by ... To know what database roles a user is part of, ... >>> determine which Windows groups a user belongs to if logging into the ...
    (microsoft.public.sqlserver.security)
  • Re: Permission to Users under DB
    ... public and db_owners database roles. ... The user mapped to dbo ... as being a member of db_owners role although the default ... permissions in the database will be the same. ...
    (microsoft.public.sqlserver.security)