Principals (Database Engine)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
Principals are entities that can request SQL Server resources. Like other components of the SQL Server 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). This topic applies to all versions of SQL Server, but there are some restrictions on server-level principals in SQL Database or Azure Synapse Analytics.
Note
Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).
SQL Server-level principals
- SQL Server authentication Login
- Windows authentication login for a Windows user
- Windows authentication login for a Windows group
- Microsoft Entra authentication login for a Microsoft Entra user
- Microsoft Entra authentication login for a Microsoft Entra group
- Server Role
Database-level principals
- Database User (There are 12 types of users. For more information, see CREATE USER.)
- Database Role
- Application Role
sa Login
The SQL Server sa
login is a server-level principal. By default, it is created when an instance is installed. Beginning in SQL Server 2005 (9.x), the default database of sa is master. This is a change of behavior from earlier versions of SQL Server. The sa
login is a member of the sysadmin
fixed server-level role. The sa
login has all permissions on the server and cannot be limited. The sa
login cannot be dropped, but it can be disabled so that no one can use it.
dbo User and dbo Schema
The dbo
user is a special user principal in each database. All SQL Server administrators, members of the sysadmin
fixed server role, sa
login, and owners of the database, enter databases as the dbo
user. The dbo
user has all permissions in the database and cannot be limited or dropped. dbo
stands for database owner, but the dbo
user account is not the same as the db_owner
fixed database role, and the db_owner
fixed database role is not the same as the user account that is recorded as the owner of the database.
The dbo
user owns the dbo
schema. The dbo
schema is the default schema for all users, unless some other schema is specified. The dbo
schema cannot be dropped.
public Server Role and Database Role
Every login belongs to the public
fixed server role, and every database user belongs to the public
database role. When a login or user has not been granted or denied specific permissions on a securable, the login or user inherits the permissions granted to public on that securable. The public
fixed server role and the public
fixed database role cannot be dropped. However you can revoke permissions from the public
roles. There are many permissions that are assigned to the public
roles by default. Most of these permissions are needed for routine operations in the database; the type of things that everyone should be able to do. Be careful when revoking permissions from the public login or user, as it will affect all logins/users. Generally you should not deny permissions to public, because the deny statement overrides any grant statements you might make to individuals.
INFORMATION_SCHEMA and sys Users and Schemas
Every database includes two entities that appear as users in catalog views: INFORMATION_SCHEMA
and sys
. These entities are required for internal use by the Database Engine. They cannot be modified or dropped.
Certificate-based SQL Server Logins
Server principals with names enclosed by double hash marks (##) are for internal system use only. The following principals are created from certificates when SQL Server is installed, and should not be deleted.
- ##MS_SQLResourceSigningCertificate##
- ##MS_SQLReplicationSigningCertificate##
- ##MS_SQLAuthenticatorCertificate##
- ##MS_AgentSigningCertificate##
- ##MS_PolicyEventProcessingLogin##
- ##MS_PolicySigningCertificate##
- ##MS_PolicyTsqlExecutionLogin##
These principal accounts do not have passwords that can be changed by administrators as they are based on certificates issued to Microsoft.
The guest User
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 its CONNECT permission. The CONNECT permission can be revoked by executing REVOKE CONNECT FROM GUEST;
within any database other than master
or tempdb
.
Related Tasks
For information about designing a permissions system, see Getting Started with Database Engine Permissions.
The following topics are included in this section of SQL Server Books Online:
See Also
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