LOGINPROPERTY – Getting to know your SQL Logins even more

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


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.


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

[sql] 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:

[sql] 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:


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:

[sql] IF OBJECT_ID('dbo.login_properties') IS NOT NULL DROP FUNCTION dbo.login_properties;
CREATE FUNCTION dbo.login_properties(@login_name NVARCHAR(MAX))
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:

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


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: , , , , ,

One Response to LOGINPROPERTY – Getting to know your SQL Logins even more

  1. Pingback: How to Re-Create a Login with only a Hashed Password - sqlity.net

Leave a Reply