Authentication Methods

The process of being validated by SQL Server is known as authentication, and it can be handled in two ways. The method you choose depends on whether the operating system or SQL Server will authorize the connection.

Windows Authentication Mode

Windows Authentication mode can be used in Windows 2000 and NT 4.0 environments to facilitate connection to SQL Server. In this mode, connections to SQL Server are validated based on the Windows account requesting the connection. SQL Server checks whether an associated login account is in the sysxlogins tables, and if there is, the connection is allowed. This is known as a trusted connection, as SQL Server has trusted the domain controller to authenticate the connection. This has the advantage of providing a single login account and the ability to leverage domain security features, such as password length and expiration, account locking, encryption, and auditing.

Mixed Authentication Mode

As its name implies, mixed mode allows Windows and SQL Server Authentication. When running in mixed mode, an additional dialog box is presented allowing you to enter a SQL Server login account and password. Mixed mode is useful for supporting legacy applications that connect using a SQL Server account and in environments in which a Windows domain controller doesn't control network access, such as in a NetWare network.

Setting the Authentication Mode

The authentication mode can be selected during or after installation by right-clicking a server and selecting Properties, Security. The Security tab allows you to specify Windows and SQL Server (mixed) or Windows-only connections. You are also given the option to set the level of auditing you want to record on login attempts. Note that when changing the authentication mode or auditing level, the change will not take effect until SQL Server is restarted. Figure 15.1 shows the Security tab of the Server Properties dialog box.

Figure 15.1. Changing the authentication mode.

Permissions

After you are connected to SQL Server, the actions that can be performed are dependent on the permissions that have been assigned. These permissions can be assigned directly to users or groups, or through roles, which are used to group related permissions.

Permissions that allow you to create and manipulate objects are referred to as statement permissions. These permissions, such as CREATE DATABASE , CREATE TABLE , and ALTER TABLE , are also referred to as Data Definition Language ( DDL ) commands. Members of the sysadmin, db_owner, and db_securityadmin roles are the only ones who can grant statement permissions.

The permissions used to access objects are referred to as object permissions. These permissions, such as SELECT , INSERT , UPDATE , and DELETE , are the permissions typically assigned to users of a database, and are also referred to as Data Manipulation Language (DML) permissions.

Some permissions cannot be granted through either statement or object-level permissions. These are referred to as predefined permissions and are obtained through membership in fixed roles or as the owner of database objects.

Fixed roles, such as the sysadmin role, have set permissions that cannot be changed or applied to other user accounts. The only way to obtain all the permissions implied by the role is to become a member of the role.

The owner of an object also has implicit permissions on the object. If, for example, a user creates a view, then that user has full permission to manage the view. No explicit permissions need to be set for a user to access or manage objects he owns.