Friday, February 10, 2012

SQL Server Login Properties

I was searching for something and I stumbled upon this script which returns a login's properties like created time, password details, whether it is active etc.,

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