Security in SQL Server 2005 is based on principals, securables, and permissions.
Principals are security accounts that can access the system.
Securables are resources within the system.
Permissions allow a principal to perform a particular action on a securable.
Let’s examine each of these concepts in more detail, starting with principals.
There are three levels of principal in a SQL Server system; the Microsoft Windows level, the SQL Server level, and the database level.
Principals at the Windows level include Windows groups, domain user accounts, and local user accounts.
At the SQL Server level, there are logins and server roles. Logins can be Windows logins or SQL Server logins. By default, only Windows logins are enabled.
Windows accounts are mapped to Windows logins in SQL Server.
Both Windows logins and SQL Server logins can be assigned to server roles.
This makes it easier to manage large numbers of users who need similar permissions.
Passwords for Windows logins are validated by Windows and can be restricted using a policy assigned to the associated Windows account.
This policy is managed by Windows and enforces restrictions on password complexity, expiration, and so on.
Passwords for SQL Server logins are validated by SQL Server, and in this release they can be restricted by using password policies that are managed by SQL Server.
Password policies are defined as part of the new CREATE LOGIN statement.
At the database level, there are users, database roles, and application roles.
Logins are mapped to database users and users can be added to one or more database roles.
Application roles are used to set an alternative security context based on the client application.
Securables also exist at different levels.
At the Windows level, securables relating to SQL Server consist of the files and registry keys that SQL Server uses.
At the SQL Server level, securables are organized into a hierarchy of scopes. The top-level scope is the server scope.
This scope correlates to the SQL Server principals level.
The server scope includes securables such as logins, HTTP endpoints, certificates, and event notifications
The server scope also includes one or more databases, which represent the next level of scope.
The database scope includes securables such as services, assemblies, and XML schemas.
Also at the database scope is the schema securable. A database can contain one or more schemas, each acting as a namespace for objects and the lowest securable scope.
The schema scope contains securables such as tables, views, and procedures.
Permissions are used to allow principals to perform actions on securables.
At the Windows level, Windows Access Control Lists, or ACLs are used to grant or deny permissions.
In SQL Server, the GRANT, REVOKE, and DENY statements are used to control the actions that principals can perform on securables.
The specific permissions that can be granted depend on the individual securable. This release of SQL Server includes a number of new permissions that apply to different securables and scopes.
Permissions granted at each scope are implicitly inherited by securables in lower scopes.
For example, a login that is granted the CONTROL permission on a database will automatically have all the permissions associated with the DBO database role in that database and a database user granted SELECT permission on a schema will automatically have SELECT permission on all securables in that schema.
