Microsoft's new security model sends mixed signals

Security in any computing environment is a system of processes, people, and machines. Security for any size organization doesn't just happen; you must plan it. Part of that scheme is database security enabling different types of users to access different levels of data. SQL Server 7.0 has new features to let you plan database security with more precision—and more complexity—than you can with SQL Server 6.5.


The basis of the Windows NT and SQL Server security architectures is a system of users and groups. In addition, SQL Server 7.0 introduces several types of roles, including fixed-server and fixed-database roles—sets of permissions associated with a specific type of functionality. Roles are the hottest new part of SQL Server 7.0 security. A group is the unit of security within NT, and the database role performs that function within SQL Server 7.0. Groups can contain NT users and other groups, but roles are more inclusive and might contain SQL Server logins, NT groups and users, and other roles.

Plan your security by arranging users into groups and database roles; this setup lets you allow or deny access to whole ranks of users at once. Together, groups and roles are powerful tools for database security: Users are in an NT group for Windows NT security access needs, and database roles for specific SQL Server access.

Integration with NT

SQL Server 7.0 security is better integrated with NT and provides greater flexibility than earlier versions of SQL Server. To access SQL Server 7.0, a user passes through two levels of security, a process similar to earlier versions. First, users encounter the authentication system. Second, users encounter the permissions system.

The authentication process verifies that the user has the authority to connect to SQL Server, and to use a database. Two server authentication modes exist:

  • NT authentication: When users attempt to connect to SQL Server using their Windows NT account, SQL Server verifies that the Windows NT account is authentic, and then verifies that either the Windows NT user (or a group the user is a member of) has been authorized to log in to SQL Server. A connection using a Windows NT account is referred to as a trusted connection because SQL Server trusts that Windows NT has properly verified the user's identity and authenticity.
  • Mixed-mode (Windows NT and SQL Server) authentication: Mixed mode lets users connect to SQL Server using either the Windows NT account or a SQL Server account (referred to as SQL Server authentication). Users who connect using a Windows NT account can use trusted connections in either NT or mixed mode. However, when a user connects to SQL Server over a nontrusted connection (meaning they attempt to log in using a SQL Server login account), SQL Server checks to see whether the login account and password are valid. (The documentation states this process enables backward compatibility, but this process is also a requirement for connections from non-NT systems and Internet clients. The phrasing in the documentation suggests Microsoft will remove nontrusted security over time—not a great omen for mixed-platform systems. However, if Kerberos (in Windows 2000, formerly Windows NT 5.0) handles future security validation, non-NT systems providing authenticated Kerberos tickets will be able to access SQL Server—at least theoretically.

After you have successfully logged in to SQL Server, you must switch to a database. You must have a valid user account in each database you wish to connect to. This user account is separate from your login. Every database (except Model) that ships with SQL Server 7.0 has a guest account, so anyone with a valid SQL Server login can connect to them. However, if you create a new database, it won't have a guest account by default, and no one except the database creator will have access. Database access can be granted to a SQL Server login, a Windows NT user, or a Windows NT group.

After security has authenticated the account, you enter the permissions validation stage. There, SQL Server defines the activities the user has permission to perform.

Microsoft is moving in the direction of Windows NT integrated security, and strongly suggests that you use Windows NT integrated security whenever possible. Do note, however, that if you are running the desktop edition of SQL Server on Windows 95 or Windows 98, Windows NT integrated security is not available, and you will have to use SQL Server Authentication security.


The four types of roles in SQL Server 7.0 are fixed server roles, fixed database roles, user-defined roles, and application roles. Let's examine each one.

Most companies formally conduct database work with distinct functional assignments. SQL Server 7.0 has structured these assignments into roles, from the server level down. The concept is sensible, but many users' work will likely stretch across multiple SQL Server roles, which Screen 1 (page 57) shows.

Roles are a great idea, but they have drawbacks. On the plus side, users can have more than one active role at a time, and a user can belong to more than one role in a database. Roles can contain NT groups and users, SQL Server users, and other roles. Security is more scalable with roles, and aliases are no longer necessary (although they are still supported for backward compatibility).

In earlier versions of SQL Server, an alias lets a user temporarily assume the identity of another user within the database. Thus, the user can perform actions as the aliased user. One purpose of this design was to let a backup DBA temporarily impersonate the dbo if that user were unavailable, or simply to support multiple DBAs in a database. However, in some environments several users have been aliased to dbo permanently, which lets them have complete control of the entire database. This approach has advantages for senior developers who are trusted users of a database. However, roles have replaced aliases. You can use roles to replace this functionality (add the senior developers to the db_owner role if you must), but you can also give them much more granular rights than you could with aliasing.

Many basic maintenance tasks, such as creating databases or managing files, required sa privilege until now. Thus, either the database administrator (DBA) performed daily basic tasks and management, or less experienced or skilled users had to have sa privilege. This arrangement was a problem for both security and human resources. Now, you can assign those users to an appropriate role, restricting security and making it more manageable.

On the negative side, the overhead of complex roles seems too great for small systems. If you have only a few actual database management roles and can match them to SQL Server 7.0's role concepts, the system is easier to control. But for large corporate environments, even planning the roles could be a nightmare. Predefined roles mean almost all users will have several roles most of the time. Keeping track of this security system across hundreds of servers and thousands of users is a major challenge.

Fixed server roles. SQL Server defines these roles at the server level and they exist independently of any individual database. Fixed server roles are permissions containers. They each have internal permissions that you are unable to give to users or logins with the GRANT command. To give users the rights to perform some operations on SQL Server, you simply add them to the appropriate role. Any member of a fixed server role can add other logins to that role. Restricting these roles to a small group of users is a good idea. You need to maintain a balance between the need to accomplish work and the need to restrict access. Generally, users are not malicious, but in an environment in which it's easy to damage entire business systems, you must carefully manage roles, especially server roles. In SQL Server 6.5, if you connected with system administrator rights you were always shown as the standard security login, sa. SQL Server 7.0 introduces the sysadmin fixed server role. You need to add any logins that need system administrator rights as members of this role. The sa login still exists for standard security mode on the desktop and for backward compatibility. Additionally, SQL Server 7.0 has six other fixed server roles, each containing a subset of the capabilities of the sysadmin role. Obviously, for a small administration team, these roles are not needed; sysadmin will likely be the only role needed. Table 1 shows the privileges associated with fixed server roles.

Fixed database roles. SQL server defines these roles at the database level for each database (Table 2 shows the privileges associated with each fixed database role). Each member of a fixed database role has the role's permissions. The db_owner fixed database role in version 7.0 is functionally equivalent to the dbo user in earlier versions of SQL Server. Again, a dbo user is the one and only user who actually owns the database. Just as with the sysadmin role, anyone who needs full administrative control of a database should be added to the db_owner fixed-database role.

Unlike fixed server roles, only a member of the db_owner role can add another user to a role.

User-defined database roles. When a group of users needs to perform a specific set of tasks in SQL Server and no equivalent NT group exists (or you are using SQL Server logins), you can add a role to the database. This role exists only in that database. For example, a company with many branches might create a user-defined role for branch staff to view customer information, but not more sensitive data, within its main database.

Application roles. SQL Server implements application security at the database level. Application roles allow you to turn off the currently logged in users' permissions, and enable only the permissions that have been given to an application role (and, of course, to public). Application roles may be password protected, and once they are enabled, they are enforced until you log out of SQL Server. Application roles are great for controlling users and their use of the database, no matter what application you use to access SQL Server 7.0. Some situations, however, require customizing security to deal with specific applications, especially those with complex databases or particularly large tables. Microsoft created application roles for the corporate market in which many users need various levels of data and application security is critical. They are particularly well suited for three-tier environments, such as when you're using Microsoft Transaction Server (MTS) components or Internet Information Server (IIS) to access SQL Server.

Users should access data only through applications. Normally, only small or relaxed environments let users access ad hoc querying. Application roles manage application access. The major differences between application roles and the other roles are:

  1. You cannot add users to application roles; they gain permissions by using the application.
  2. You can optionally protect application roles with a password (which is a good idea).
  3. When users activate an application role, they temporarily lose all permissions to the user account and other groups or roles they belong to.

Application roles effectively limit users while they use applications, providing a high degree of security.

How to Set Up Roles and Modify Role Membership

You can set all these roles in either Transact SQL (T-SQL) or Enterprise Manager. To add logins to fixed server roles, select the server required, expand the security folder, and then highlight the attribute Server Roles, as in Screen 1. Right-click on the role you wish to modify and select Properties. Select the General tab and click Add (if you are adding a new role). You will see all available logins in the list. Select the login you wish to add to this role. To add a user to a fixed database role, expand the databases folder and expand the database required. Click on the Roles option to see all available roles in the database. Again, select properties for the role you wish to modify, and just as before you can add or remove users from any role. To add a new user-defined or application role, select the server and database required, right-click on the Role option, and select New Database Role (you will see a screen similar to Screen 2). You can designate the role as an application role (and optionally set a password) or a standard role. Click Add to add new users to a standard role. After you create a role, you can open it and click the permissions button. This action will bring up a screen similar to Screen 3. Now you can set permissions appropriate to the role within the database. This option is not available for fixed database or fixed server roles.

The SQL Server Create Login Wizard

SQL Server's Create Login Wizard is easy to use and has enough onscreen information for inexperienced users; however, don't let beginners near it. The Security Wizard lets users select an authentication mode and grant access to security roles and databases. This wizard is another feature of SQL Server 7.0 Microsoft aimed at the unskilled user and small company market in an attempt to simplify complex and advanced software. Surely, no DBA will use this tool to assign roles to all users. The Security Wizard doesn't include all the available configurations and finer details. DBAs in organizations with many users will avoid this wizard.

Assigning Permissions

SQL Server 7.0 includes permissions similar to permissions in SQL Server 6.5: object permissions, such as SELECT and UPDATE; statement permissions, such as CREATE PROCEDURE; and implied permissions, which only members of roles can perform.

Permissions are assigned with the GRANT, REVOKE, and DENY statements. GRANT permits a user to work with objects or to issue statements. (SQL Server 7.0 doesn't permit cross-database permissions because permissions are assigned to users, not logins.) DENY is a new statement and a reserved word; it prevents users from gaining permission through any other GRANT. DENY always overrides any other grant­i.e., if you have the select permission on the authors table, but you belong to the sales role, which has been denied select on authors, you won't be able to select from the authors table. This is true regardless of the order in which the statements were issued. REVOKE removes a previous Grant or Deny.

On objects, DENY has a specific parameter, CASCADE, which cascades the denial through a tree of accounts starting with the specified account. This parameter denies permission to the specified account and to all those accounts granted permission by the account being denied. You must use CASCADE when you issue a DENY on a grantable permission; otherwise, an error message is returned.

Granting object permissions requires forethought. When you grant permission to a user (NT user, NT group, SQL Server user, or SQL Server role) for an object, you can optionally specify the WITH GRANT OPTION parameter. GRANT WITH GRANT OPTION lets an account give the same permission to any other user account. After you use this parameter, you no longer have control over which accounts receive that permission. The situation can become a recursive nightmare. However, the saving grace is, by default, only members of the db_owner or db_securityadmin roles, or the object's owner can issue a GRANT to use an object to another user. GRANT... WITH GRANT OPTION applies to both role and object permissions.

Permissions are cumulative in SQL Server 7.0. If a user is a member of multiple roles (or Windows NT groups that have been given database permissions), the user receives the cumulative total of those permissions. The exception to this is that DENY is always preventative, regardless of other rights a user may have. This applies to PUBLIC as well, so don't DENY select to PUBLIC (because then no one can access the object).

More Flexible, More Complex

Although many features of SQL Server 7.0 try to reduce the level of skill required to support it, other facilities increase difficulty. Security has become much more flexible in the new version, but it has also increased in complexity.