Now that you understand the tools available to you for securing your data within SQL Server 2000, you must design an access and permissions strategy that works for your organization. In this lesson, you will learn access and permissions strategies incorporating these tools, and the strengths and weaknesses of each strategy in different environments.
First, you need to determine which users will need full access to SQL Server 2000. In general, limit the number of users to whom you grant full access. To grant these users full access, you have a number of choices. If these users are currently members of the local Administrators group on the SQL Server 2000 computer (by default, domain administrators are members of the local Administrators local group), they already have full access as members of the sysadmin server role. If they are not members of this local group, you can do one of the following:
In general, as the number of Windows 2000 users who will be SQL Server 2000 system administrators increases and as the number of computers running SQL Server 2000 increases, the more useful a dedicated Windows 2000 global security group becomes. Using a Windows group allows you to grant a user administrator access merely by adding him or her to this Windows group.
In addition, you might not want all members of the local Administrators group to have full access to SQL Server 2000. In this case, remove the login for the local BUILTIN\Administrators group. However, before you remove this login, make make sure you have provided alternate administrator access (generally with a dedicated SQL Server Administrator group login).
For users that require administrator access to SQL Server 2000, but for whom you do not want to grant full administrator access, use server roles to grant them the rights they absolutely require. Use combinations of server roles to grant sufficient permissions. Consider using built-in groups and creating Windows groups in which to place users and map these Windows groups to SQL Server 2000 server roles to simplify administration. Remember that adding a user or group to a server role automatically grants them login access to SQL Server 2000. Also remember that you must use BUILTIN rather than the domain or computer name to add a built-in group.
First, enable Mixed Mode authentication only if absolutely necessary. If you must use Mixed Mode authentication, consider enabling SSL encryption for all communication. Create SQL Server 2000 security accounts for each SQL Server login required. Add these SQL Server logins to server roles as required, using the minimum permissions necessary.
Next, grant login access to the Windows users that will need access to SQL Server 2000. Consider creating and granting login access to one Windows 2000 group that will contain all Windows 2000 users requiring access to SQL Server 2000. Also consider creating a second Windows 2000 group that will contain all users that are specifically denied access to SQL Server 2000. Create a login for this Windows 2000 group and specifically deny login access to any members of this group. Using only these two groups will allow you to quickly grant or deny any Windows user access to SQL Server 2000.
First, if one user will have responsibility for a particular database, make that user the owner of the database. Next, create user accounts in the database for users that require database access. If you use SQL Server Enterprise Manager, you can only add users who have SQL Server 2000 logins. If you use Transact-SQL, you can add any Windows 2000 user or group. Consider creating Windows groups for each type of user requiring database access with different levels of permissions.
Add Windows users and groups, and SQL Server logins (if any) to fixed database roles to provide access rights to perform specific tasks. Create Windows groups as appropriate and map these to fixed database roles.
If you plan to use certain fixed database roles in all new databases, consider adding the roles to the model database and then adding the appropriate users. For example, consider creating a Windows group for security operators and then adding that group to the db_securityadmin fixed database role in the model database. Each new database created will then automatically have the db_securityadmin role automatically populated with this Windows group.
There are several levels to consider in providing data access. First, determine whether you want to provide guest access to the database. You can create a guest user account in the database and grant the guest user limited rights to view certain tables, views, columns, and stored procedures. You have no way to audit the activities of a specific guest user because the user is not accessing the database through his or her own user account (although you can audit guest user activity). Any rights granted or denied the guest user account have no impact on permitted users.
Next, determine the data access rights that are required by all permitted users in the database. Grant these rights to the public role. This gives you a single place to grant these rights and makes administration easier. All permitted users acquire these rights by default upon connecting to the database.
Next, if certain users or groups require additional access rights to tables, views, stored procedures, and functions, you have a number of options.
Finally, if you need to limit access to tables based on columns, consider using views or stored procedures rather than restricting individual columns. This will generally result in improved performance.
SQL Server 2000 provides a myriad of ways to implement security. Choosing the method most appropriate to your environment depends upon fully understanding the options available to you with Windows 2000 (or Windows NT) and SQL Server 2000. In general, choose an access and permissions strategy that enables you to set security in as few places as possible. This will simplify the task of updating and modifying permissions, as well as debugging security problems should they occur.