locked
Accessing database through public role RRS feed

  • Question

  • I'm using SQL Server 2005 with Windows authentication. I have a Window's user who is able to connect to a database eventhough the user's login is not mapped to the database. The user belongs to a Windows group that is again not mapped to the database. I've been investigating and found that the user connects through the database's public role. The public role has permissions defined. How can this be? I thought that the login must be given database access and it would then be automatically assigned to public. Surely I'm missing something.
    Monday, March 22, 2010 9:48 PM

Answers

  •   I can see two possibile explanations:

    a) The Windows user is a member of sysadmin (i.e. via group membership), this would give access to any database as DBO. SELECT user_name() would return "dbo" in this case.

    b) The guest user is enabled on the database. SELECT user_name() would return "guest" in this case.

      If none of these cases are true, there must be another explanation. I would recommend lookign at the user token (SELECT * FROM sys.user_token) as a first step to fidn out how this principal got access to the DB.

      I hope this information helps.

     -Raul Garcia
      SDE/T
      SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, March 22, 2010 10:19 PM

All replies

  •   I can see two possibile explanations:

    a) The Windows user is a member of sysadmin (i.e. via group membership), this would give access to any database as DBO. SELECT user_name() would return "dbo" in this case.

    b) The guest user is enabled on the database. SELECT user_name() would return "guest" in this case.

      If none of these cases are true, there must be another explanation. I would recommend lookign at the user token (SELECT * FROM sys.user_token) as a first step to fidn out how this principal got access to the DB.

      I hope this information helps.

     -Raul Garcia
      SDE/T
      SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, March 22, 2010 10:19 PM
  • Thanks Raul. The user was entering as guest.
    Tuesday, March 23, 2010 1:48 PM