LOGINPROPERTY – Getting to know your SQL Logins even more

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:

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:

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:

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:

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.