Review/Unlock SQL accounts

Whenever passwords expire, people always seem to lockout there account, and seemed strange that to unlock you have to set the password again. As a DBA I care not to know other people's passwords, less liability on me ;-). So I whipped this up. It allows one to review the current account status and decide whether or not to "@UnlockAccounts". 

review:
SET NOCOUNT ON

DECLARE
    @UserName VARCHAR(128)
  , @SQLUnlock VARCHAR(8000)
  , @UnlockAccounts Bit 
SELECT
    @UserName = NULL --'bob'
    , @UnlockAccounts = 0
 

IF OBJECT_ID('tempdb..#tmpLogins') IS NOT NULL 
    DROP TABLE #tmpLogins
SELECT
    p.NAME
  , LOGINPROPERTY(p.name, 'IsLocked ') [IsLocked]
  , LOGINPROPERTY(p.name, 'IsExpired') [IsExpired]
  , LOGINPROPERTY(p.name, 'DaysUntilExpiration') [DaysUntilExpiration]
  , LOGINPROPERTY(p.name, 'IsMustChange ') [IsMustChange]
  , LOGINPROPERTY(p.name, 'BadPasswordCount') [BadPasswordCount]
  , LOGINPROPERTY(p.name, 'BadPasswordTime') [BadPasswordTime]
  , LOGINPROPERTY(p.name, 'LockoutTime') [LockoutTime]
  , LOGINPROPERTY(p.name, 'PasswordLastSetTime') [PasswordLastSetTime]
  , master.sys.fn_varbintohexstr(l.password_hash) pHASH
  , p.TYPE [Type]
INTO
    #tmpLogins
FROM
    master.sys.server_principals p
    LEFT JOIN master.sys.sql_logins l ON p.principal_id = l.principal_id    
WHERE
    p.type IN ('S')
    --AND p.name NOT IN ('sa')
    AND p.name = ISNULL(@UserName, p.name)
    --AND LOGINPROPERTY(p.name, 'IsLocked') <> 0 

SELECT
    *
FROM
    #tmpLogins TL

IF NOT EXISTS ( SELECT
                    Name
                FROM
                    #tmpLogins
                WHERE
                    [IsLocked] = 1 ) 
    BEGIN
        PRINT 'No accounts Locked'
 --RETURN
    END

SELECT
    @SQLUnlock = REPLACE((
                       SELECT
                        '
--//------------------------------------------
--// ' + TL.NAME + '
  ALTER login [' + TL.name
                        + '] with check_expiration = OFF, check_policy = OFF;
  --GO
  ALTER login [' + TL.name + '] '
                        + CASE WHEN TL.type IN ('U', 'G') THEN 'from windows '
                               ELSE ''
                          END + 'with '
                        + CASE WHEN TL.type = 'S'
                               THEN 'password = ' + TL.pHASH
                                    + ' hashed UNLOCK '
                          END + ';
  --GO
  ALTER login [' + TL.name
                        + '] with check_expiration = ON, check_policy = ON;'
                       FROM
                        #tmpLogins TL
                       WHERE
                        [IsLocked] = 1
                      FOR
                       XML PATH('')
                      ), '
', '')
PRINT @SQLUnlock
IF @UnlockAccounts = 1 
BEGIN
 EXEC(@SQLUnlock)
 PRINT 'Unlocked'
END 
~Enjoy

Comments :

0 comments to “Review/Unlock SQL accounts”