Post reply

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

  • December 5, 2018 at 12:14 pm

    #380963

    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!

  • December 5, 2018 at 1:16 pm

    #2015602

    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.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • December 5, 2018 at 1:31 pm

    #2015604

    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.

  • December 5, 2018 at 3:00 pm

    #2015615

    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 3 (of 3 total)

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