Post reply

User able to connect even when not assigned to the database using Windows Authentication

  • June 10, 2010 at 12:27 pm

    #219945

    Hi,

    We are using Sql Server 2005 with Windows Authentication.

    The login properties (User Mapping Screen) of one user does not show the user assigned to one of the databases.

    But he is able to connect to that database and run queries.

    I could see his account in the Activity Monitor connected to that database.

    database.sys.database_principals does not show the sid of the user. He does not have any server roles assigned.

    Could this be possible? Any thoughts please?

    Thanks,

    Madhuri

  • June 10, 2010 at 12:34 pm

    #1178846

    if the windows user has been explicitly assigned a login withthe server roles sysusers,

    or belongs to a windows gorup that has been assigned sysadmin rights (like BuiltIn\Administrators),

    then that user would have access to every database, and need not appear in the lsit of users that actually exist in the database itself.

    take a look at

    select * from master.sys.server_principals

    and see what windows groups exist there.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 10, 2010 at 1:09 pm

    #1178873

    you are correct. That user is a member of builtin\administrators group.

    Thanks for your quick response.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply