I can't figure out how a user is logging in!

  • C-M-A

    Valued Member

    Points: 60

    I have a 2005 server with several Windows individuals and group logins. One user in particular who is NOT listed with a login and is NOT part of the Windows groups is logged in and running queries on one of this instances databases.

    I checked his user in AD and confirmed he is not a member of any of the groups we have entries for in this instance. I also confirmed as best I could that none of the groups he is a member of is in turn a member of one of the groups allowed connection to this instance. I checked for orphaned users, but found none.

    I'm struggling to find answers online . Is there some kind of dangerous setting that allows any Windows credentials to connect to this instance and run queries on our databases? despite not explicitly being set to?

    Thanks in advance!

  • Jeffrey Williams 3188

    SSC Guru

    Points: 87951

    SQL Server 2005 has a couple of groups added that are local to the actual machine - not domain groups - added as logins, not to mention the BUILTIN\Administrators group.

    If this individual is a local administrator on the server - or has added himself to one of the local groups - then that individual would have sysadmin access to the instance.  To fix this you would disable/remove the BUILTIN\Adminstrators and/or remove the individual from those groups.  However - if the individual is an administrator on the machine they could just add themselves back...

    To prevent that specific user - add his domain account as a login and disable (DENY CONNECT) that login.  That should prevent the user from accessing the instance...as long as you can disable or remove the BUILTIN\Administrators.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • C-M-A

    Valued Member

    Points: 60

    Thank you Jeffrey! That was exactly what it is. The user is a member of another group that happens to have admin access to this server.

  • Chris Harshman

    SSC-Forever

    Points: 41616

    If you're not sure how a windows authenticated login is getting access to a database, there's actually a command to show all the access paths:
    EXEC xp_logininfo N'mydomain\myuser','all';

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

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