Lesson 4: Overview of SQL Server Security
SQL Server validates users at two levels: login authentication and permissions validation. Login authentication ensures that the user is a known, valid user of the SQL Server. Permissions validation checks that the user is authorized to use a particular statement or object in a database. This lesson describes these two levels of security.
After this lesson, you will be able to
- Describe login authentication
- Describe permission validation
- Describe SQL Server database users and roles
Estimated lesson time: 30 minutes
Login Authentication
A user must have a login account to connect to SQL Server. SQL Server recognizes two login authentication mechanisms—SQL Server authentication and Windows NT authentication—both of which have a different type of login account (see Figure 1.11).
Figure 1.11 Two types of login authentication
SQL Server Authentication
When using SQL Server authentication, a SQL Server system administrator defines a SQL Server login account and password. Users must supply both the SQL Server login and the password when they connect to SQL Server.
Windows NT Authentication
When using Windows NT authentication, a Windows NT user or group account controls user access to SQL Server—a user does not provide a SQL Server login account when connecting. A SQL Server system administrator must grant either the Windows NT user account or the Windows NT group account access to SQL Server.
Authentication Modes
When SQL Server is running on Windows NT, a system administrator can specify that it run in one of two authentication modes: Windows NT Authentication Mode or Mixed Mode.
NOTE
Previous versions of SQL Server supported three authentication modes. In SQL Server 7, Standard Mode is no longer supported.
Windows NT Authentication Mode
When the SQL Server is using Windows NT Authentication Mode, only Windows NT authentication is allowed. Users cannot specify a SQL Server login account. This mode is not supported by a SQL Server installed on Windows 95 or Windows 98.
Mixed Mode
When the SQL Server is using Mixed Mode, users can connect to SQL Server with Windows NT authentication or SQL Server authentication.
NOTE
Clients connecting to a server running in Windows NT Authentication Mode must use a Windows NT_authenticated connection. Clients connecting to a server running in Mixed Mode can choose an authentication mechanism.
Permission Validation
SQL Server accepts Transact-SQL statements after a user's login has been successfully authenticated. Each time a user sends a statement, SQL Server checks that the user has permission to carry out the action requested by the statement. If the user has permission, the action is carried out; if not, an error is returned to the user. Figure 1.12 illustrates this process.
Figure 1.12 Permission validation in SQL Server
NOTE
In most cases, users will be interacting with an application user interface, unaware of the Transact-SQL statements that their actions are generating.
Database User Accounts and Roles
User accounts and roles, which identify a successfully logged-on user within a database, are used to control ownership of objects. Permissions to execute statements and to use objects in a database are granted to users and roles. Each user account is mapped to a SQL Server login (see Figure 1.13).
Figure 1.13 Database users and roles
Database User Accounts
The user accounts used to apply security permissions are mapped to Windows NT user or group accounts or to SQL Server login accounts. User accounts are specific to a database and cannot be used in other databases.
Database Roles
Roles enable you to assemble users into a single unit to which you can apply permissions. Both server-level and database-level roles exist. A user can be made a member of more than one role.
SQL Server provides predefined server and database roles for common administrative functions, so that you can easily assign a selection of administrative permissions to a particular user simply by making the user a member of the appropriate roles. You can also create your own user-defined database roles.
NOTE
Roles replace the SQL Server version 6.5 concepts of aliases and groups.
