bschelst/ February 11, 2019/ SQL-Server/ 0 comments

Sometimes you need to know when a SQL login was created. The SQL code below will list the SQL logins, when this was created, and if this is enabled or disabled.

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
Share this Post

Leave a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>
*
*

nineteen − 18 =