| title | Principals (Database Engine) | Microsoft Docs | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ms.custom | |||||||||||||||
| ms.date | 06/13/2017 | ||||||||||||||
| ms.prod | sql-server-2014 | ||||||||||||||
| ms.reviewer | |||||||||||||||
| ms.technology | security | ||||||||||||||
| ms.topic | conceptual | ||||||||||||||
| f1_keywords |
|
||||||||||||||
| helpviewer_keywords |
|
||||||||||||||
| ms.assetid | 3f7adbf7-6e40-4396-a8ca-71cbb843b5c2 | ||||||||||||||
| author | VanMSFT | ||||||||||||||
| ms.author | vanto | ||||||||||||||
| manager | craigg |
Principals are entities that can request [!INCLUDEssNoVersion] resources. Like other components of the [!INCLUDEssNoVersion] authorization model, principals can be arranged in a hierarchy. The scope of influence of a principal depends on the scope of the definition of the principal: Windows, server, database; and whether the principal is indivisible or a collection. A Windows Login is an example of an indivisible principal, and a Windows Group is an example of a principal that is a collection. Every principal has a security identifier (SID).
Windows-level principals
-
Windows Domain Login
-
Windows Local Login
SQL Server-level principals
-
[!INCLUDEssNoVersion] Login
-
Server Role
Database-level principals
-
Database User
-
Database Role
-
Application Role
The [!INCLUDEssNoVersion] sa log in is a server-level principal. By default, it is created when an instance is installed. Beginning in [!INCLUDEssVersion2005], the default database of sa is master. This is a change of behavior from earlier versions of [!INCLUDEssNoVersion].
Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable.
Every database includes two entities that appear as users in catalog views: INFORMATION_SCHEMA and sys. These entities are required by [!INCLUDEssNoVersion]. They are not principals, and they cannot be modified or dropped.
Server principals with names enclosed by double hash marks (##) are for internal system use only. The following principals are created from certificates when [!INCLUDEssNoVersion] is installed, and should not be deleted.
-
##MS_SQLResourceSigningCertificate##
-
##MS_SQLReplicationSigningCertificate##
-
##MS_SQLAuthenticatorCertificate##
-
##MS_AgentSigningCertificate##
-
##MS_PolicyEventProcessingLogin##
-
##MS_PolicySigningCertificate##
-
##MS_PolicyTsqlExecutionLogin##
Each database includes a guest. Permissions granted to the guest user are inherited by users who have access to the database, but who do not have a user account in the database. The guest user cannot be dropped, but it can be disabled by revoking it's CONNECT permission. The CONNECT permission can be revoked by executing REVOKE CONNECT FROM GUEST within any database other than master or tempdb.
By definition, a client and a database server are security principals and can be secured. These entities can be mutually authenticated before a secure network connection is established. [!INCLUDEssNoVersion] supports the Kerberos authentication protocol, which defines how clients interact with a network authentication service.
The following topics are included in this section of [!INCLUDEssNoVersion] Books Online:
Securing SQL Server
sys.database_principals (Transact-SQL)
sys.server_principals (Transact-SQL)
sys.sql_logins (Transact-SQL)
sys.database_role_members (Transact-SQL)
Server-Level Roles
Database-Level Roles