Principals

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).

Windows-level principals

  • Windows Domain Login
  • Windows Local Login

SQL Server-level principal

  • SQL Server Login

Database-level principals

  • Database User
  • Database Role
  • Application Role

The SQL Server sa Login

The SQL Server sa login is a server-level principal. It is created by default when an instance is installed. In Microsoft SQL Server 2005, the default database of sa is master. This is a change of behavior from previous versions of Microsoft SQL Server.

public Database Role

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.

INFORMATION_SCHEMA and sys

Every database includes two entities that appear as users in catalog views: INFORMATION_SCHEMA and sys. These entities are required by SQL Server. They are not principals, and 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##

See Also

Reference

Server-Level Roles
Database-Level Roles

Concepts

IDs and SIDs

Other Resources

sys.database_principals (Transact-SQL)
sys.server_principals (Transact-SQL)
sys.sql_logins (Transact-SQL)
sys.database_role_members (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance