Moving from SQL Server 6.5 to 7.0 will force you to change not only the way you manage and access data, but also the way you secure it. SQL Server 7.0 has a radically different way of validating logins and assigning permissions to users that will require you to rethink your security plan. That's the bad news. The good news is that with a little planning, securing your data can be much easier than with SQL Server 6.5.

Authentication and Network Libraries

The concept of Windows NT authentication hasn't changed in SQL Server 7.0. The primary benefit is that SQL Server 7.0 receives the access token that NT generates when a user logs in. Because the access token contains the login account's SID and the list of groups the account is a member of, you can now manage SQL Server login security through the use of domain global groups.

The use of NT authentication doesn't stop at logins; you can also use NT accounts to protect database objects. In SQL Server 7.0, you can grant both object and statement permissions to an NT account or group. As with NTFS permissions, all members of a group receive the group's permissions, and a user's total set of permissions is the sum of all the group and individual permissions.

SQL Server's permission rules are like those of NTFS in another way—the DENY permission supersedes permissions granted individually or through membership in a group. However, the DENY statement applies specifically to SELECT, UPDATE, DELETE, INSERT, and EXECUTE permissions and to statement permissions. Unlike the No Access NTFS permission, DENY doesn't globally prevent all access to the object or statement. It just overrides the permission denied.

SQL Server authentication is part of SQL Server 7.0, but as an alternative to NT authentication and not as a separate mode. You have a choice of using strictly NT authentication or SQL Server and NT authentication together (similar to SQL Server 6.5's mixed-mode security). You can effectively have only SQL Server logins by removing all NT-authenticated logons from the server.

Significantly, SQL Server 7.0 supports SQL Server 6.5 logins unchanged, and you don't need to change any of your login accounts when you upgrade. This continuity means that your users can continue to log in while you migrate them to NT-authenticated login accounts.

In SQL Server 6.5, only the Named Pipes and Multiprotocol network libraries support NT authentication. SQL Server 7.0 added the TCP/IP library to the list to support the networks that run TCP/IP. So you can now use the best-performing network library for both NT and SQL Server authenticated logins.

Roles

Roles are independent of the server's authentication mode. In many ways, they act like NT local groups. Within databases, roles replace SQL Server 6.5 database groups and offer two useful improvements. First, a user can belong to other roles besides Public. All users are still part of the Public role, which operates like the Everyone local group in NT. Second, users receive the permissions of all the groups they're members of, and these permissions combine with permissions assigned to individual users and to NT groups.

At the server administration level, SQL Server 7.0 defines some built-in server roles. The sa account still exists, but it now gets permissions from its membership in the sysadmins server role. Any other account, a SQL Server or an NT logon, can also be a member of that role and receive the same permissions. You can give other administrators serverwide permissions without having to share the sa password. A side effect is that you can eliminate the anonymity of the sa login and audit who makes changes to your system.

You can also use built-in database roles. The most significant is the db_owner role, which contains the list of members who can act as database owner (DBO). The DBO user account still exists, but now it's usually assigned to the sa login account. The db_owner role replaces the practice of aliasing user accounts to DBO to give other users total control over the database.

You can make your security management task less complicated by managing access through NT domain global groups and database roles. If you have only NT-authenticated logins, you can remove database roles from the plan because you can assign permissions directly to the global group. But, you'll want to avoid removing roles when you need the permissions granted to a built-in role such as db_owner. For mixed logins, database roles are a convenient way to group both kinds of logins together and assign permissions as a whole instead of independently.

For a server using only NT authentication, the process is straightforward because all permissions come from membership in a domain global group. For mixed environments, you'll need to maintain a separate security database in SQL Server, but database permissions for both kinds of logins come from membership in roles. With a little planning, even a complicated security plan can be easy to implement in SQL Server 7.0.