We are in the process of upgrading a Microsoft product that just hasn’t gotten around to integrating with AD and uses SQL accounts. It also has a really bad habit of locking SQL accounts if your AD policy for failed login attempts is set low. So I got tired of searching through SSMS to unlock accounts, so I wrote a quick script to list the locked accounts and the command to unlock it. I didn’t go as far as to auto-unlock the locked accounts but that would be a simple edit. Here’s the script.
Declare @UserID varchar(25), @status nvarchar(5), @Command varchar(255), @command1 varchar(255)
Declare Account_cursor Cursor
for select name from sys.syslogins where name not like ‘<DOMAIN>\%’
FETCH NEXT FROM Account_cursor
WHILE @@FETCH_STATUS = 0
select @status= convert(nvarchar,(loginproperty(@userID,N’IsLocked’)))
if @status = ’1′
print ‘– ‘ + @userID + ‘ is locked’
select @command = ‘ALTER LOGIN [' + @userID + '] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF, NO CREDENTIAL’
select @command1 = ‘ALTER LOGIN [' + @userID + '] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON, NO CREDENTIAL’
– exec (@command)
– exec (@command1)
fetch next from Account_Cursor into @userID
I am guessing that this is not the easiest or most efficient way to do this, so if anyone would like to educate me on a better way, I am very open to learning your techniques.