Post reply

A simple question

  • August 24, 2016 at 9:41 am

    #320911

    Dear fellow SQL team,

    Thanks for looking into my question.

    I have a simple question.

    How do I find out if a Windows login is disabled in SQL Server or not.

    I can use master.sys.sql_logins.is_disabled for SQL Logins. But Not able to figure it out for Windows logins.

    Please help.

    Thanks

  • August 24, 2016 at 9:50 am

    #1897233

    You can't disable a Windows login, but you can revoke the CONNECT permission.

    John

  • August 24, 2016 at 8:31 pm

    #1897368

    Just to be clear here...

    You can't disable the actual Windows Login from SQL Server (well, you can but only if the server has Domain Admin privs and you really know how to use xp_CmdShell and NET USER, etc) [font="Arial Black"]BUT [/font]you can disable a Windows Login that has been "registered" on SQL Server and only on the SQL Server. It won't feed back to the Windows AD server.

    The problem is that you're not finding the Windows Logins by using sys.sql_logins because it's only designed to "Returns one row for every SQL Server authentication login."

    https://msdn.microsoft.com/en-us/library/ms174355(v=sql.110).aspx

    What you really need to be using to find disabled logins on the SQL Server is sys.server_principals, which will list all logins and other server level principles.

    https://msdn.microsoft.com/en-us/library/ms188786.aspx

    If you want to find Windows Authentication Logins on SQL Server that are no longer valid in Windows AD, make a call to sp_validatelogins.

    https://msdn.microsoft.com/en-us/library/ms181728.aspx

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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