locked
Login is disabled RRS feed

  • Question

  • Twice in the last month an application sql server login has been disabled and we don't know who or how. Does anyone know how we can either prevent or track this?
    Alan
    Sunday, January 15, 2012 4:31 AM

Answers

  • Locked or disabled? 

    Check out who has  an appropriate  permissions to do that (disable login)

    Requires ALTER ANY LOGIN permission.

    If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission.

    If the login that is being altered is a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission when making the following changes:

    • Resetting the password without supplying the old password. 
    • Enabling MUST_CHANGE, CHECK_POLICY, or CHECK_EXPIRATION. 
    • Changing the login name. 
    • Enabling or disabling the login. 
    • Mapping the login to a different credential.

    A principal can change the password, default language, and default database for its own login.


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Elmozamil Elamir Sunday, January 15, 2012 6:15 AM
    • Marked as answer by Maggie Luo Thursday, February 2, 2012 7:52 AM
    Sunday, January 15, 2012 5:11 AM

All replies

  • Locked or disabled? 

    Check out who has  an appropriate  permissions to do that (disable login)

    Requires ALTER ANY LOGIN permission.

    If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission.

    If the login that is being altered is a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission when making the following changes:

    • Resetting the password without supplying the old password. 
    • Enabling MUST_CHANGE, CHECK_POLICY, or CHECK_EXPIRATION. 
    • Changing the login name. 
    • Enabling or disabling the login. 
    • Mapping the login to a different credential.

    A principal can change the password, default language, and default database for its own login.


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Elmozamil Elamir Sunday, January 15, 2012 6:15 AM
    • Marked as answer by Maggie Luo Thursday, February 2, 2012 7:52 AM
    Sunday, January 15, 2012 5:11 AM
  • So that's where your ALTER ANY LOGIN question is coming from. I don't think that's a very good idea: if the person or process that disables the login, is a member of the sysadmin role, DENY will not help you.

    It's better to track down the root cause. The script below sets up a trace that captures the Audit Server Principal Management event. Let the script run. When it happens again, stop the trace:

    exec sp_trace_setstatus @TraceID, 0
    exec sp_trace_setstatus @TraceID, 2

    Then open the trace file in Profiler. You will need to make a record of the trace id, but you can also get it from sys.traces.

    NOTE! You will need to modify the script to have a usable file name, see the comments in the script!

    /****************************************************/
    /* Created by: SQL Server 2008 Profiler                         */
    /* Date: 2012/01/15    11:44:09                 */
    **************************************************
    
    -- Create a Queue
    declare @rc int
    declare @TraceID int
    declare @maxfilesize bigint
    set @maxfilesize = 5
    
    -- Please replace the text InsertFileNameHere, with an appropriate
    -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
    -- will be appended to the filename automatically. If you are writing from
    -- remote server to local drive, please use UNC path and make sure server has
    -- write access to your network share
    
    exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
    if (@rc != 0) goto error
    
    -- Client side File and Table cannot be scripted
    
    -- Set the events
    declare @on bit
    set @on = 1
    exec sp_trace_setevent @TraceID, 177, 7, @on
    exec sp_trace_setevent @TraceID, 177, 23, @on
    exec sp_trace_setevent @TraceID, 177, 39, @on
    exec sp_trace_setevent @TraceID, 177, 8, @on
    exec sp_trace_setevent @TraceID, 177, 40, @on
    exec sp_trace_setevent @TraceID, 177, 64, @on
    exec sp_trace_setevent @TraceID, 177, 1, @on
    exec sp_trace_setevent @TraceID, 177, 9, @on
    exec sp_trace_setevent @TraceID, 177, 41, @on
    exec sp_trace_setevent @TraceID, 177, 49, @on
    exec sp_trace_setevent @TraceID, 177, 10, @on
    exec sp_trace_setevent @TraceID, 177, 26, @on
    exec sp_trace_setevent @TraceID, 177, 34, @on
    exec sp_trace_setevent @TraceID, 177, 42, @on
    exec sp_trace_setevent @TraceID, 177, 50, @on
    exec sp_trace_setevent @TraceID, 177, 3, @on
    exec sp_trace_setevent @TraceID, 177, 11, @on
    exec sp_trace_setevent @TraceID, 177, 35, @on
    exec sp_trace_setevent @TraceID, 177, 43, @on
    exec sp_trace_setevent @TraceID, 177, 51, @on
    exec sp_trace_setevent @TraceID, 177, 4, @on
    exec sp_trace_setevent @TraceID, 177, 12, @on
    exec sp_trace_setevent @TraceID, 177, 28, @on
    exec sp_trace_setevent @TraceID, 177, 60, @on
    exec sp_trace_setevent @TraceID, 177, 5, @on
    exec sp_trace_setevent @TraceID, 177, 21, @on
    exec sp_trace_setevent @TraceID, 177, 29, @on
    exec sp_trace_setevent @TraceID, 177, 37, @on
    exec sp_trace_setevent @TraceID, 177, 45, @on
    exec sp_trace_setevent @TraceID, 177, 6, @on
    exec sp_trace_setevent @TraceID, 177, 14, @on
    
    -- Set the Filters
    declare @intfilter int
    declare @bigintfilter bigint
    
    -- Set the trace status to start
    exec sp_trace_setstatus @TraceID, 1
    
    -- display trace id for future references
    select TraceID=@TraceID
    goto finish
    
    error:
    select ErrorCode=@rc
    
    finish:
    go

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, January 15, 2012 10:49 AM
  • Was that because of windows password policy? Check in ERRORLOG if there are failed login attempts and does that relate with locking time? What is the domain policy for password lockout?
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Sunday, January 15, 2012 5:54 PM