yogirk

SQL Server Login properties

Feb 10th, 2012
186
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.29 KB | None | 0 0
  1. DECLARE @name NCHAR(100)
  2. DECLARE @LoginTable TABLE (
  3.     LoginName NCHAR(100)
  4.     ,PasswordLastSetTime SQL_VARIANT
  5.     ,IsExpired SQL_VARIANT
  6.     ,IsLocked SQL_VARIANT
  7.     ,IsMustChange SQL_VARIANT
  8.     ,LockoutTime SQL_VARIANT
  9.     ,BadPasswordcount SQL_VARIANT
  10.     ,BadPasswordTime SQL_VARIANT
  11.     ,HistoryLength SQL_VARIANT
  12.     ,PasswordHash SQL_VARIANT
  13.     ,PasswordLastSet SQL_VARIANT
  14.     )
  15.  
  16. DECLARE Login_Cursor CURSOR
  17. FOR
  18. SELECT NAME
  19. FROM syslogins
  20.  
  21. OPEN Login_Cursor
  22.  
  23. FETCH NEXT
  24. FROM Login_Cursor
  25. INTO @name
  26.  
  27. WHILE @@FETCH_STATUS = 0
  28. BEGIN
  29.     INSERT INTO @LoginTable
  30.     SELECT @name AS 'LoginName'
  31.         ,LOGINPROPERTY(@name, 'PasswordLastSetTime') AS PasswordLastSetTime
  32.         ,LOGINPROPERTY(@name, 'IsExpired') AS IsExpiried
  33.         ,LOGINPROPERTY(@name, 'IsLocked') AS IsLocked
  34.         ,LOGINPROPERTY(@name, 'IsMustChange') AS IsMustChange
  35.         ,LOGINPROPERTY(@name, 'LockoutTime') AS LockoutTime
  36.         ,LOGINPROPERTY(@name, 'BadPasswordCount') AS BadPasswordCount
  37.         ,LOGINPROPERTY(@name, 'BadPasswordTime') AS BadPasswordTime
  38.         ,LOGINPROPERTY(@name, 'HistoryLength') AS HistoryLength
  39.         ,LOGINPROPERTY(@name, 'PasswordHash') AS PasswordHash
  40.         ,LOGINPROPERTY(@name, 'PasswordLastSetTime') AS PasswordLastSet
  41.  
  42.     FETCH NEXT
  43.     FROM Login_Cursor
  44.     INTO @name
  45. END
  46.  
  47. CLOSE Login_Cursor
  48.  
  49. DEALLOCATE Login_Cursor
  50.  
  51. SELECT *
  52. FROM @LoginTable
Advertisement
Add Comment
Please, Sign In to add comment