Database Principals

Database principals are the entities that can request access to SQL Server resources. Principals include logins, server roles, database users, database roles, and application roles.

Understanding Principals

In SQL Server, security is managed through principals. A principal is an entity that can be authenticated by SQL Server. When a principal attempts to access a resource, SQL Server checks its permissions to determine if access should be granted. The primary types of principals are:

Logins: These are security principals at the server level. Logins are required to connect to an instance of SQL Server. They can be Windows logins (which rely on Windows authentication) or SQL Server logins (which use credentials managed by SQL Server).
Users: These are security principals at the database level. Database users are mapped to server-level logins, allowing them to access specific databases.
Roles: Roles are collections of permissions that can be assigned to users or other roles. This simplifies permission management by allowing you to grant a set of permissions to a role, and then assign users to that role. Roles can be server-level (server roles) or database-level (database roles).
Application Roles: These are special types of roles that are activated by passing a password. They are often used to provide a specific set of permissions to an application without requiring end-users to have individual logins.

Key Concepts

Principals and Hierarchy: Logins operate at the server level, while users operate at the database level. A user is typically associated with a login. Server roles are also server-level principals, while database roles are database-level principals.

Mapping Logins to Users: When you create a database user, you must associate it with a server-level login. This mapping is crucial for authentication and authorization. A user can have different permissions in different databases, even if they are mapped to the same login.

Permissions: Permissions define what actions a principal can perform on a securable (e.g., a table, view, or stored procedure).

Common Principal Types and Usage

1. Logins

Logins are the first line of defense. They authenticate your connection to the SQL Server instance.

Example (SQL Server Login):

CREATE LOGIN MyLogin WITH PASSWORD = 'ComplexPassword123!';

Example (Windows Login):

CREATE LOGIN [MYDOMAIN\MyUser] FROM WINDOWS;

2. Database Users

Database users are created within a specific database and are mapped to server logins.

Example (Creating a user mapped to a SQL Server login):

USE MyDatabase;
CREATE USER MyUser FOR LOGIN MyLogin;

Example (Creating a user mapped to a Windows login):

USE MyDatabase;
CREATE USER MyUser FROM LOGIN [MYDOMAIN\MyUser];

3. Database Roles

Database roles simplify permission management within a database.

Example (Creating a custom database role):

USE MyDatabase;
CREATE ROLE ReadOnlyUsers;

Example (Adding a user to a role):

USE MyDatabase;
ALTER ROLE ReadOnlyUsers ADD MEMBER MyUser;

Note: Built-in database roles like db_datareader and db_datawriter provide common sets of permissions.

4. Application Roles

Application roles provide a mechanism to activate a set of permissions by calling a stored procedure with a password.

Example (Creating an application role):

USE MyDatabase;
CREATE APPLICATION ROLE AppReader WITH PASSWORD = 'SecureAppPassword';

To activate this role:

EXEC sp_setapprole 'AppReader', 'SecureAppPassword';

Important: Proper management of principals and their associated permissions is fundamental to securing your SQL Server databases. Always follow the principle of least privilege, granting only the necessary permissions.

Microsoft Docs