sys.sql_logins – Getting to know your SQL Logins

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

Introduction

In SQL Server Logins and the sys.server_principals catalog view I introduced the catalog view sys.server_principals. It returns all logins and server roles defined on the server. There is one category of server principals that, while it is included in sys.server_principals, has additional properties that are not returned by this catalog view. That category is "SQL Logins".

sys.sql_logins Example

SQL Logins are those logins for which SQL Server does the entire authentication work and related actions by itself. For the other login type, Windows Logins, SQL Server delegates the authentication process to Windows. That means that SQL Server needs to hold additional information like the password for SQL Logins. Some of those additional properties are made available in the sys.sql_logins catalog view.

To demonstrate that catalog view, we first need to create a SQL Login:

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

With that SQL Login in place we can now look at sys.sql_logins:

SELECT  SL.name,
        SL.principal_id,
        SL.sid,
        SL.type,
        SL.type_desc,
        SL.is_disabled,
        SL.create_date,
        SL.modify_date,
        SL.default_database_name,
        SL.default_language_name,
        SL.credential_id,
        SL.is_policy_checked,
        SL.is_expiration_checked,
        SL.password_hash 
  FROM sys.sql_logins AS SL

There are quite a few columns returned by this view. However, if you compare it to sys.server_principals you notice that most of those columns are repeated. In fact there are only three new columns contained in sys.sql_logins. The following query just selects those columns in addition to the name:

SELECT  SL.name,
        SL.is_policy_checked,
        SL.is_expiration_checked,
        SL.password_hash 
  FROM sys.sql_logins AS SL

For our example SQL Login it produces this output:

sys.sql_logins – Getting to know your SQL Logins

The password_hash column contains the password, which is stored as a salted hash. The is_policy_checked column tells us if the windows password policies were enforced when the password was set. Similarly, the is_expiration_set column indicates if SQL Server will expire the password based on the windows password expiration settings.

Summary

The sys.sql_logins catalog view can be used to retrieve additional information on SQL Logins that is not included in the sys.server_principals catalog view. It contains three additional columns that return the hashed password itself as well as information about the enforcement of the windows password policy and the windows password expiration policies.

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

0 comments

Trackbacks

  1. […] We know already that the passwords are hashed and that the hashed value can be queried using the sys.sql_logins catalog view. But, how is this value actually calculated? How do you get form a password to its […]

  2. […] Server does follow best practices and therefore 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 […]

  3. […] Did you once write an application (or procedure) that made use of the password hashes in sys.sql_logins […]