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:~EnjoySET 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
Comments :
Post a Comment