SQL Server 7 ignores user permissions

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Jon Ley
    #1

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


    Tags: None
    • Simon Hayes
      #2
      Re: SQL Server 7 ignores user permissions


      "Jon Ley" <no.email@nospa m.com> wrote in message
      news:3f86c93a$0 $8762$ed9e5944@ reading.news.pi pex.net...[color=blue]
      > 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[/color]
      far[color=blue]
      > 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[/color]
      have[color=blue]
      > full access to all of the tables.
      >
      > What am I missing here??
      >
      >[/color]

      One possibility is that Domain Users is in the local Administrators group on
      the server (BUILTIN\Admini strators) - 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


        Comment

        • Jon Ley
          #3
          Re: SQL Server 7 ignores user permissions


          "Simon Hayes" <sql@hayes.ch > wrote in message
          news:3f86dfe0$1 _1@news.bluewin .ch...[color=blue]
          >
          > One possibility is that Domain Users is in the local Administrators group[/color]
          on[color=blue]
          > the server (BUILTIN\Admini strators) - that group is in the sysadmin role[/color]
          by[color=blue]
          > 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?
          >[/color]

          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_denydataread er and db_denydatawrit er 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.


            Comment

            • Jon Ley
              #4
              Re: SQL Server 7 ignores user permissions

              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...[color=blue]
              >
              > One possibility is that Domain Users is in the local Administrators group[/color]
              on[color=blue]
              > the server (BUILTIN\Admini strators) - that group is in the sysadmin role[/color]
              by[color=blue]
              > 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
              >
              >[/color]


                Comment

                Previous template Next
                Working...