What if your server has hundreds of logins and you need to quickly get an overview of all the logins? There may be better ways, but I shamelessly stole the above script and put it inside a cursor, just for for fun :) Enjoy!
DECLARE @name NCHAR(100) DECLARE @LoginTable TABLE ( LoginName NCHAR(100) ,PasswordLastSetTime SQL_VARIANT ,IsExpired SQL_VARIANT ,IsLocked SQL_VARIANT ,IsMustChange SQL_VARIANT ,LockoutTime SQL_VARIANT ,BadPasswordcount SQL_VARIANT ,BadPasswordTime SQL_VARIANT ,HistoryLength SQL_VARIANT ,PasswordHash SQL_VARIANT ,PasswordLastSet SQL_VARIANT ) DECLARE Login_Cursor CURSOR FOR SELECT NAME FROM syslogins OPEN Login_Cursor FETCH NEXT FROM Login_Cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO @LoginTable SELECT @name AS 'LoginName' ,LOGINPROPERTY(@name, 'PasswordLastSetTime') AS PasswordLastSetTime ,LOGINPROPERTY(@name, 'IsExpired') AS IsExpiried ,LOGINPROPERTY(@name, 'IsLocked') AS IsLocked ,LOGINPROPERTY(@name, 'IsMustChange') AS IsMustChange ,LOGINPROPERTY(@name, 'LockoutTime') AS LockoutTime ,LOGINPROPERTY(@name, 'BadPasswordCount') AS BadPasswordCount ,LOGINPROPERTY(@name, 'BadPasswordTime') AS BadPasswordTime ,LOGINPROPERTY(@name, 'HistoryLength') AS HistoryLength ,LOGINPROPERTY(@name, 'PasswordHash') AS PasswordHash ,LOGINPROPERTY(@name, 'PasswordLastSetTime') AS PasswordLastSet FETCH NEXT FROM Login_Cursor INTO @name END CLOSE Login_Cursor DEALLOCATE Login_Cursor SELECT * FROM @LoginTable
No comments:
Post a Comment