Home » Blog » sys.sql_logins – Getting to know your SQL Logins

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:

[sql] CREATE LOGIN ASqlLogin WITH PASSWORD='********';
[/sql]

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

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

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:

[sql] SELECT SL.name,
SL.is_policy_checked,
SL.is_expiration_checked,
SL.password_hash
FROM sys.sql_logins AS SL
[/sql]

For our example SQL Login it produces this output:

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.