Category Archives: 2743

List of logins with creation dates

========== SQL Server Management Studio ==========

If you have installed the *client tools* of SQL Server 2005, you can use SQL Server Management Studio to connect to your SQL Server, open the security node, and right click to apply filter settings for showing the logins. There exists a filter criterion for login creation date.

You can also view the list of logins in the summary page (View –> Summary).

–> SQL Server Management Studio
   –> Object Explorer
      –> (+) your SQL Server
         –> (+) Security
            –> (+) Logins
–> right click for filter settings
or, –> View –> Summary –> List (login names and creation dates are shown)

========== sys.server_principals ==========

Login information can also be obtained from the catalog views of SQL Server.
- sys.server_principals contains a row for every server-level principal

- master..syslogins in SQL Server 2000

- sys.syslogins in SQL Server 2005 (for backward compatibility)

SELECT
               
'Server[@Name='
                        + quotename(CAST(serverproperty(N'Servername') AS sysname),'''')
                        + ']'
                        + '/Login[@Name='
                        + quotename(log.name,'''')
                        + ']' AS [Urn]
               
, log.name AS [Name]
               
, CASE WHEN N'U' = log.type THEN 0
                        WHEN N'G' = log.type THEN 1
                        WHEN N'S' = log.type THEN 2
                        WHEN N'C' = log.type THEN 3
                        WHEN N'K' = log.type THEN 4
                        END AS [LoginType]
               
, log.is_disabled AS [IsDisabled]
               
, log.create_date AS [CreateDate]
       
FROM
               
sys.server_principals AS log
       
WHERE
               
(log.type in ('U', 'G', 'S', 'C', 'K')
                        AND log.principal_id not between 101 and 255
                        AND log.name <> N'##MS_AgentSigningCertificate##')
       
ORDER BY
               
[Name] ASC

========== For more information ==========

- sys.server_principals (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188786.aspx

- syslogins
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-l_64mr.asp