The SQL Server 7.0 security system architecture is based on users and groups of users, called security principals. The following illustration shows how Windows NT-based users and groups can map to security accounts in SQL Server, and how SQL Server can handle security accounts independent of Windows NT-based accounts.
SQL Server provides security at the database level by using individual application security. SQL Server operates in one of two security (authentication) modes:
Mixed Mode allows users to connect using Windows NT Authentication or SQL Server Authentication. Users who connect through a Windows NT-based user account can make use of trusted connections in either Windows NT Authentication Mode or Mixed Mode. After successful connection to SQL Server, the security mechanism for both modes is the same.
Security systems based on SQL Server logins and passwords can be easier to manage than security systems based on Windows NT user and group accounts, especially for nonsensitive, noncritical databases and applications. For example, a single SQL Server login and password can be created for all users of an application, rather than creating all the necessary Windows NT user and group accounts. However, this eliminates the ability to track and control the activities of individual users.
The security environment in Windows NT and SQL Server is stored, managed, and enforced through a hierarchical system of users. To simplify the administration of many users, Windows NT and SQL Server use groups and roles. A group is an administrative unit within the Windows NT operating system that contains Windows NT users or other groups. A role is an administrative unit within SQL Server that contains SQL Server logins, Windows NT logins, groups, or other roles. Arranging users into groups and roles makes it easier to grant or deny permissions to many users at one time. The security settings defined for a group are applied to all members of that group. When a group is a member of a higher-level group, all members of the group inherit the security settings of the higher-level group, in addition to the security settings defined for the group or the user accounts.
The organizational chart of a security system commonly corresponds to the organizational chart of a company, as demonstrated in this illustration.
The organizational chart for a company is a good model for the security system of a company, but there is one rule for a company's organizational hierarchy that does not apply to the security model. Common business practice usually dictates that an individual reports to only one manager. This implies that an employee can fall under only one branch of the organizational hierarchy.
The needs of a database security system go beyond this limitation because employees commonly need to belong to security groups that do not fall within the strict organizational hierarchy of the company. Certain staff members, such as administrators, can exist in every branch of the company and require security permissions regardless of organizational position. To support this broader model, the Windows NT and SQL Server security system allows groups to be defined across the hierarchy. An administrative group can be created to contain administrative employees for every branch of the company, from corporate to payroll.
This hierarchical security system simplifies management of security settings, and it allows security settings to be applied collectively to all group members without having to be defined redundantly for each person. The hierarchical model also accommodates security settings applied to only one user.