LOGINPROPERTY – Getting to know your SQL Logins even more

2014-04-07 - DMVs & CVs, General, Security, Security

Introduction

Yesterday we discovered the sys.sql_logins catalog view and saw that it returns additional information on SQL Logins that is not included in the sys.server_principals catalog view.
However, there is more information on SQL Logins available than that catalog view reveals. To get to that additional information, you can use the LOGINPROPERTY catalog function.

LOGINPROPERTY Example

For today's examples, we are going to use the same login we created yesterday:

CREATE LOGIN ASqlLogin WITH PASSWORD='********';

The LOGINPROPERTY catalog function takes two parameters. The first one, login_name is the name of the SQL Login you want to check the properties on. The second one is the property_name and it specifies which property you are after.

Let us look at an example:

SELECT LOGINPROPERTY('ASqlLogin','BadPasswordCount') AS BadPasswordCount,
       LOGINPROPERTY('ASqlLogin','IsLocked') AS IsLocked;

Those two properties let us know how many login attempts for this SQL Login have been recorded and if the account has been locked because of it. An example output is shown below:

LOGINPROPERTY in action

There are in total thirteen properties that you can check with this catalog function. To make using that function a little easier, I created a table valued function that returns all properties for a given SQL Login name:

IF OBJECT_ID('dbo.login_properties') IS NOT NULL DROP FUNCTION dbo.login_properties;
GO
CREATE FUNCTION dbo.login_properties(@login_name NVARCHAR(MAX))
RETURNS TABLE
AS
RETURN SELECT
LOGINPROPERTY(@login_name,'BadPasswordCount') AS [BadPasswordCount],
LOGINPROPERTY(@login_name,'BadPasswordTime') AS [BadPasswordTime],
LOGINPROPERTY(@login_name,'DaysUntilExpiration') AS [DaysUntilExpiration],
LOGINPROPERTY(@login_name,'DefaultDatabase') AS [DefaultDatabase],
LOGINPROPERTY(@login_name,'DefaultLanguage') AS [DefaultLanguage],
LOGINPROPERTY(@login_name,'HistoryLength') AS [HistoryLength],
LOGINPROPERTY(@login_name,'IsExpired') AS [IsExpired],
LOGINPROPERTY(@login_name,'IsLocked') AS [IsLocked],
LOGINPROPERTY(@login_name,'IsMustChange') AS [IsMustChange],
LOGINPROPERTY(@login_name,'LockoutTime') AS [LockoutTime],
LOGINPROPERTY(@login_name,'PasswordHash') AS [PasswordHash],
LOGINPROPERTY(@login_name,'PasswordLastSetTime') AS [PasswordLastSetTime],
LOGINPROPERTY(@login_name,'PasswordHashAlgorithm') AS [PasswordHashAlgorithm];

I usually place that function in the master database as the logins are stored there too, but it works in other databases too. An explanation for each of the properties you can find in BOL.

You can use the dbo.login_properties function for example by cross applying it to the sys.sql_logins catalog view like this:

SELECT SL.name,LP.* 
  FROM sys.sql_logins AS SL
 CROSS APPLY dbo.login_properties(SL.name) AS LP;

Summary

The sys.sql_logins catalog view does not return all authentication related properties for a SQL Login. The LOGINPROPERTY catalog function closes that gap by providing an additional twelve properties. This article introduced the dbo.login_properties table valued function that returns all LOGINPROPERTY values for a given SQL Login.

Categories: DMVs & CVs, General, Security, Security
Tags: , , , , ,

0 comments

Trackbacks

  1. […] only stores a salted hash of the password. In sys.sql_logins – Getting to know your SQL Logins and LOGINPROPERTY – Getting to know your SQL Logins even more I showed you two ways to retrieve the hashed password for a SQL […]