Post reply

ALTER LOGIN for all SQL account with "Enforce password expiration" turned on

  • October 12, 2015 at 8:45 am

    #399171

    I am having a problem with an application which is creating SQL accounts with the "Enforce password policy" and "Enforce password expiration" selected. The new user accounts should not have the "Enforce password expiration" selected. I know that I can simply go to the SQL account and deselect the "Enforce password expiration" option. However, I do not know when the application admin will create a new SQL account.

    I found this script which searches for any account with the "Enforce password policy" and "Enforce password expiration" = 1. However, this script will only create the Alter Login command for each account. How do I modify this script to alter the accounts during execution?

    USE master

    SELECT 'ALTER LOGIN ' + '[' + name + '] ' + ' WITH

    CHECK_EXPIRATION = OFF; 'FROM sys.sql_logins

    WHERE is_policy_checked = 1

    and is_expiration_checked = 1

  • October 12, 2015 at 8:51 am

    #1832815

    DECLARE @sSQL NVarchar(4000) = ''

    SELECT @sSQL = @sSQL + 'ALTER LOGIN ' + '[' + name + '] ' + ' WITH CHECK_EXPIRATION = OFF; '

    FROM sys.sql_logins

    WHERE is_policy_checked = 1

    AND is_expiration_checked = 1;

    EXEC (@sSQL)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 7, 2015 at 4:56 pm

    #1838436

    Gail's solution works, but it is similar to closing the barn door after the cow got out. Your application should not have been granted the ability to create logins - that is a serious security flaw :).

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

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