An Overview of System Function LOGINPROPERTY for SQL Login Details

By: Manvendra Singh   |   Updated: 2022-01-28   |   Comments   |   Related: 1 | 2 | 3 | 4 | > Security

Problem

When creating a SQL Server login there are many different properties that can be set for the login. You could use the SSMS GUI to navigate through the screens to see various settings for a login, but in this article we will cover the LOGINPROPERTY function to quickly return details about a SQL Server login.

Solution

 I am going to discuss the system function LoginProperty which is very useful in getting various SQL Server login-related settings. Although, you can get most of the information returned by this function through other methods like using SQL Server Management Studio, system catalog views, or other SQL Server security-related system stored procedures or functions.

Understanding SQL Server LoginProperty Function

The LoginProperty system function returns details about SQL Server login policy settings. Below is the syntax of this function.

--LoginProperty function Syntax
--login_name is the name of login about which you need details
--property_name is the name of settings that you want to check
LOGINPROPERTY ('login_name', 'property_name')

This function requires two parameters: the first parameter is the login name you want to check and the second parameter is the name of property you want to check.

We can fetch 13 different property values for any SQL Server login using this system function. These properties are listed below.

• DefaultDatabase - the database the login connects to as a default
• IsExpired - if the login is active or not
• DaysUntilExpiration - number of days until password expires
• BadPasswordCount - number of times a bad password was provided
• BadPasswordTime - the last time the a wrong password was used
• HistoryLength - indicates number of past passwords stored for login
• IsLocked - if login is currently locked out from making a connection
• LockoutTime - this is the date and time login was locked out
• PasswordHash - a hash value of the current password
• IsMustChange - states the password needs to be changed on next login
• DefaultLanguage - the default language login uses when connected
• PasswordLastSetTime - last time the password was changed
• PasswordHashAlgorithm - indicates the type of password hash algorithm used

Create Test Login

Let's create a login to use to check the above properties.

Run the below T-SQL to create the

• SQL login "manvendra"
• Default database as master
• Default language as US English
• Password policy set to on
• Expiration set to off

I also assigned the sysadmin server role to the login to perform various administrative activities.

-- Create a login named manvendra and assign sysadmin server role 
USE [master]
GO

CREATE LOGIN [manvendra] 
   WITH PASSWORD=N'C0d!ng$!ght', 
        DEFAULT_DATABASE=[master], 
        DEFAULT_LANGUAGE=[us_english], 
        CHECK_EXPIRATION=OFF, 
        CHECK_POLICY=ON
GO

--Assign sysadmin role to login manvendra
ALTER SERVER ROLE [sysadmin] ADD MEMBER [manvendra]
GO

I executed the above as shown below.

Check Default Database of a Login

Suppose you want to check the default database setting for a login, this can be done as follows:

--Get the default database of a SQL Server login
SELECT LOGINPROPERTY('manvendra', 'DefaultDatabase') AS [Default Database];  
GO

The above command returns 'master' as the default database.

Let me change the default database for this login to database AdventureWorksDW2019.

ALTER LOGIN [manvendra] WITH DEFAULT_DATABASE=[AdventureWorksDW2019]

We can then do something like the following to use this function to make sure the login has master as default database:

-- Check default database for a login
IF
LOGINPROPERTY('manvendra', 'DefaultDatabase') != 'master'
   PRINT 'Change default database for your login to master database'
ELSE
   PRINT 'Default database is already set to master database'
GO

Based on the above logic, we can see the output of the above query shows we should change the default database to the master database.

Check Login Expiration

The next use case of this system function would be to check whether a specific login is expired using "IsExpired".

-- Check login 'manvendra' is expired or active
SELECT LOGINPROPERTY('manvendra', 'IsExpired') AS [Expired];  
GO

The above T-SQL statement will return.

• 1 - if login is expired
• 0 - if login is active

The below image shows that login 'manvendra' is active as it has returned a 0.

Using 'DaysUntilExpiration' will return the number of days until the password will expire.  Below are possible values this property will return based on the nature of the specified login.

1. 0 - if login is already expired or going to expire that day
2. -1 - if the local security policy in Windows is set to never expires
3. NULL - if the CHECK_POLICY or CHECK_EXPIRATION is OFF for a login or if the operating system does not support the password policy
4. A number greater than 1 for the actual days to expiration

-- Check login 'manvendra' will expire after how many days
SELECT 
   LOGINPROPERTY('manvendra', 'IsExpired')           AS [Password Expired],
   LOGINPROPERTY('manvendra', 'DaysUntilExpiration') AS [Expire After Days]
GO

Based on the current settings we get the following:

Let's make a slight change to the settings as follows.

ALTER LOGIN [manvendra] WITH CHECK_EXPIRATION=ON, CHECK_POLICY=ON

If we run this again, we will see the following since the server has a local security policy. Here the output shows the login is active and will expire in 42 days.

Audit Bad Password Attempts

This function is useful in getting bad password-related information like when you or somebody attempted to connect to your SQL Server instance using this login with a wrong password and how many attempts were made with wrong passwords using that login.

There are two properties to get this information:

• BadPasswordTime – This shows the last attempt made with an incorrect password
• BadPasswordCount – This shows the number of consecutive attempts made with an incorrect password

Run the below statements to get the details.

-- Get Last Attempt with Incorrect password & Number of Attempts with Incorrect Password
SELECT 
   LOGINPROPERTY('manvendra', 'BadPasswordTime')  AS [Last Attempt with Incorrect password],
   LOGINPROPERTY('manvendra', 'BadPasswordCount') AS [Number of Attempts with Incorrect Password]
GO

I executed the above T-SQL statement to fetch bad password details for login 'manvendra'. You can see the last login attempt and that there were 16 attempts made with an incorrect password.

Check Login Lockout Details

We can also use this function to see if a login is locked out and when it was locked out.

Below are two properties we will use to get this information:

• IsLocked – Will return 1 if login is locked and 0 if login is not locked out.
• LockoutTime – Is the date and time login was locked out after maximum number of allowed attempts.

Run below statements to get login locked out details.

-- Get Login locked out details
SELECT 
   LOGINPROPERTY('manvendra', 'IsLocked')    AS [Password Locked],
   LOGINPROPERTY('manvendra', 'LockoutTime') AS [Last Lockout Date]
GO

We can see that login 'manvendra' is not locked out and the last locked out date is the default date and time.

Check Last Password Reset Time

We can also get the last time the password was changed for a login.

--Check when was password changed
SELECT LOGINPROPERTY('manvendra', 'PasswordLastSetTime') AS [Last Password Change]

You can see the output of the above T-SQL statement to get the last time the password was changed.

Check Details for a Login

Here are a few other things that can be returned using this system function:

• DefaultLanguage – This property will return the default language set for the login.
• IsMustChange – This property tells password must change on next login. 1 means must change it and 0 means does not need to change.
• PasswordHash – If you want to get the password hash for a login.
• PasswordHashAlgorithm – This property returns the algorithm used to hash the password. 
  • 0 for SQL7.0 hash
  • 1 for SHA-1 hash
  • 2 for SHA-2 hash
  • NULL if the login is not a valid SQL Server login

Now, let's get the output for the above properties.

--Check other details for a specific login
SELECT 
   LOGINPROPERTY('manvendra', 'DefaultLanguage')       AS [Default Language],
   LOGINPROPERTY('manvendra', 'IsMustChange')          AS [Password Must Change],
   LOGINPROPERTY('manvendra', 'PasswordHash')          AS [Password Hash],
   LOGINPROPERTY('manvendra', 'LockoutTime')           AS [PasswordLastSetTime],
   LOGINPROPERTY('manvendra', 'PasswordHashAlgorithm') AS PasswordHashAlgorithm
GO

Here is the output.

Next Steps

• I have shown you various use cases of for the LOGINPROPERTY function. This can be very useful to gather information and to also audit your SQL Server logins. Try this on your system to see if you have any issues with your logins.
• SQL Server Security Tips

About the author

Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips