Principals (Database Engine)
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 version of SQL Server, but there are some restictions on server-level principals in SQL Database or SQL Data Warehouse.
SQL Server-level principals
- SQL Server authentication Login
- Windows authentication login for a Windows user
- Windows authentication login for a Windows group
- Azure Active Directory authentication login for a AD user
- Azure Active Directory authentication login for a AD group
Database User (There are 11 types of users. For more information, see CREATE USER.)
- Database Role
- Application Role
The SQL Server
sa log in is a server-level principal. By default, it is created when an instance is installed. Beginning in SQL Server 2005 , 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 database 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
dbo use 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
dbouser 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.
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:
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.
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 it's CONNECT permission. The CONNECT permission can be revoked by executing
REVOKE CONNECT FROM GUEST; within any database other than
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:
Securing SQL Server