Home » Blog » SQL Server Logins and the sys.server_principals catalog view

SQL Server Logins and the sys.server_principals catalog view

2014-01-02 - DMVs & CVs, Fundamentals, General, Security, Security

Introduction

SQL Server utilizes a two level authentication system: Logins and Users. Logins are used to connect to a SQL Server instance while Users are used to determine the active permissions within a database.

Both logins and users are considered security principals within SQL Server. Logins are server principals while users are database principals.

List of all logins and roles

To get the list of all logins for a server you can use this query:

[sql] SELECT SP.name,
SP.principal_id,
SP.sid,
SP.type,
SP.type_desc,
SP.is_disabled,
SP.create_date,
SP.modify_date,
SP.default_database_name,
SP.default_language_name,
SP.credential_id,
SP.owning_principal_id,
SP.is_fixed_role
FROM sys.server_principals AS SP;
[/sql]

sys.server_principals returns one row for every security principal that is defined for the current SQL Server instance.

There are three main types of server principals: The login principals, the group principals and the certificate principals.

The login principals are the ones you can use to actually connect to the instance. They include the Windows Logins and the SQL Logins.

Group principals include Windows Groups and Server Roles. They are used to grant (or deny) permissions to a group of logins. While a group login cannot directly be used to establish a connection to a SQL Server instance, all members in a windows group will inherit the permissions of that group if a server principal for that group was created.

Certificate based logins also cannot be used to connect to the instance. Instead, they can for example be used to grant permissions to T-SQL modules directly. There are also two types: Principals that were created using an asymmetric key and principals that were created using an actual certificate.

SID

Most of the columns of the sys.server_principals catalog view are fairly self-explanatory. However, there are two that are worth an explanation: SID and owning_principal_id. The later indicates for server roles, which login was used to create them. Server Roles that are not so-called "fixed server roles" but instead end-user created roles are a new feature in SQL Server 2012. The is_fixed_role column can be used to distinguish fixed roles from user created roles.

The SID is the globally unique security identifier of the principal. For windows logins it is equivalent to the windows SID of that login. For all other principals it is a unique identifier similar to a GUID. However, the only place the SID is used internally by SQL Server is to link a login to a database user. Everywhere else the principal_id is used instead.

Summary

SQL Server knows six different types of server principals that can be categorized in 3 groups. All are listed in the sys.server_principals catalog view. Each one is identified by a principal_id. There is also a globally unique security identifier, but that is only used to like a database user to a login.