Public role when user added as part of Windows Group RRS feed

  • Question

  • Hi,

    I have added all our developers as a Windows Group to our db server running SQL Server 2008. If I log onto a database where I have been granted access through our developer Windows Group it seems as if I take on the public role. If I e.g. tries to run Properties on the database it says that role public can not run this query due to limited permissions. Shouldn't I be treated as my real Windows login?

    The same problem is apparent if I try to create a new database diagram as my user. The owner is public which means that I cannot see the diagram that I have created.

    If I create e.g. a new stored procedure and misses to specify the schema my username will be used as the schema for that procedure (implicit creation). Upon that procedure creation a local user in the database will be created. After that, if I try to run Properties on the database I will be authenticated as my Windows logon and the same is true if I now try to create a new database schema. Now the schema used will be my own username and I will see it.

    All this is very confusing for me, is this really right? Seems as if I am not correctly authorized through a Windows Group and I do not run things as myself.

    Best regards
    Wednesday, March 18, 2009 11:03 AM


  •  What you are experiencing is a side effect of granting access to a DB via Windows group membership that exists mainly for historic and backwards compatibility reasons.
    You are correct, when accessing the database using a Windows group and before an implicit (or explicit if you choose so) user creation takes place, your primary identity is a grey area. 

      When asking the question "who am I in the database?" the answer you may expect is the Windows name, but since there is no user (and hence no user_id) for you, the answer becomes "public". Then the natural question that arises (i.e. your original question) is "how come the DB says I am "public", if I am a valid Windows user and I got here via a group?". The best way to answer this one is by looking at the user token (select * FROM sys.user_token):

    Id |   SID   | name             |  type          |  usage
    0  |   0x... | public           |  ROLE          |  GRANT OR DENY
    10 |   0x... | DOMAIN\GroupName |  WINDOWS GROUP |  GRANT OR DENY

      The token seems correct and close to what we are expecting except for the missing primary identity (user name), but since there is no user_id, there is no way to have a valid primary identity in the token, and the only thing left are roles and Windows groups including public. Since it is necessary to have some sort of primary identity for error messages, the system chooses "public" as an answer for this scenario.

      Since the system knows that is a relatively common and expected scenario, in many cases we try a simple trick for some of our builtins such as user_name(): we return the primary identity name from the login token (which will return the Windows name) whenever it is possible, but when the user id is really needed (i.e. SELECT user_id() ) we have to keep "public" and its ID as an answer.

      I hope this answers your question.

      -Raul Garcia
       SQL Server Engine

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, March 18, 2009 8:39 PM