Thursday, October 18, 2012

Principals - SQL Server


Principals - SQL Server


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 log in is a server-level principal. By default, it is created when an instance is installed. In SQL Server 2005 and SQL Server 2008, the default database of sa is master. This is a change of behavior from earlier versions of SQL Server.
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 topublic 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.
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##
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. SQL Server supports the Kerberos authentication protocol, which defines how clients interact with a network authentication service.
For more information about the SQL Server implementation of Kerberos support, see Kerberos Authentication and SQL Server.