Authentication There are two methods of authentication offered SQL Server 2000, Windows authentication and SQL Server authentication. Windows authentication allows the SQL server authentication to be integrated directly with security on a Windows 2000/NT system. Authentication will depend on the security mode that SQL Server 2000 runs under, Windows Authentication mode, or Mixed Mode.

If you are running SQL Server 2000, you will need to configure the SQL server to run in mixed mode to allow the non-Windows 2000/NT clients to connect where it will interact with computers running operating systems other than Windows 2000 or Windows NT. Mixed mode allows clients who are not operating Windows 2000/NT to connect to a SQL 2000 server using SQL Server authentication.

If you are running in a fully Windows 2000/NT environment then you should configure the SQL server to run under the Windows Authentication security mode. In Windows Authentication mode the SQL server relies on Windows 2000/NT for security. While running under the Windows Authentication Mode, a server can utilize security features such as password aging, complexity requirements, and encryption. The SQL server grants access based on the Windows authentication of the user.

Auditing The basis of auditing in SQL Server 2000 is the server side SQL Trace and the client side viewing utilities. Auditing is configured using the SQL Profiler. With the SQL Profiler you can create a custom auditing scheme that allows you to audit nearly every event that is applied to server data and security. SQL Server 2000 allows login auditing similar to auditing available on Windows 2000/NT. Administrators can select the level of auditing that they require via the Enterprise Manager. Auditing keeps track of whenever a user is successful or unsuccessful in their attempt to log on.

C2 auditing is a special type of auditing required by some government agencies that requires that every event occurring be logged. SQL 2000 Server is capable of C2 auditing, but the overhead for maintaining this level of auditing is very high.

Creating Logins Logins are used to prevent intentional or unintentional tampering from unauthorized users. Each login has a set of sub-accounts that are linked to it that provide aliases for it. Logins can be created in three ways; in SQL Server 2000, through the Enterprise Manager, or by using T-SQL commands.

Users A user is defined for a particular database and linked to a login via a sub-account. Users can be created through the Enterprise Manager, the Login Wizard or through the use of T-SQL. Once a user is created, permissions should be assigned to that reflect the job for which it was created. Permissions are assigned to a specific user login. There are two types of permissions that can be assigned to a user. Server permissions are granted to allow certain users to perform database administration tasks. Database permissions are used to control access to database objects.

Permissions and Ownership of Database Objects Every object in a SQL database has a user who is designated as an owner or dbo. When an object is first created, the only person who has the ability to perform any operations on the object is the dbo. The dbo will grant other users permission to perform certain operations on the object. For a user to be able to manipulate, perform operations on, or even see a database object the user must have permissions to do so. Permissions on tables and views are:

• INSERT
• UPDATE
• DELETE
• SELECT
• REFERENCES

Alternatively, the owner of a database object may assign ALL permissions to grant a user all of the above. A user must have the appropriate permissions before they can execute the corresponding statement. Owners of a stored procedure can grant EXECUTE permission to a user to enable the user to run the stored procedure.

Database Roles The task of managing permissions to many database users can be simplified through the use of database roles. Using database roles allows like permissions to be assigned to many users without having them being individually assigned. Roles can be created and managed using the Enterprise Manager or through the use of T-SQL Statements. There are several predefined roles that are established when SQL Server 2000 is installed:

• sysadmin: performs any server activity
• dbcreator: creates and alter databases
• diskadmin: manages disk files
• setupadmin: manages linked servers and startup procedures
• bulkadmin: performs bulk inserts
• serveradmin: sets any server option and shut down the database
• securityadmin: manages logins and create database permissions
• processadmin: manages SQL processes

Other roles can be defined and altered by the administrator after installation. Fixed roles cannot be modified.

Managing Security through Other Means Security can be managed through other means through limiting access to objects that could affect sensitive data. Like permissions, access to functions and stored procedures can be explicitly restricted or explicitly granted. In addition there are several system stored procedures that allow an administrator to manage security more easily:

• sp_grantlogin Allows a Windows 2000/NT user to connect with Windows authentication
• sp_addapprole Adds an application security role
• sp_changeobjectowner Changes the owner of an object in a database
• sp_changedbowner Changes the owner of a database
• sp_defaultlanguage Changes the default language of a login
• sp_addlogin Adds a SQL Server login that uses SQL Server Authentication
• sp_helplogins Supplies information about logins and users associated to the database
• sp_addrolemember Adds a security account as a member of an existing database role
• sp_grantdbaccess Adds a security account to a database and allows an administrator to grant permissions

Maintaining security and protecting information from triggers that may negatively alter sensitive information can be achieved by establishing constraints. Properly established constraints can ensure that data integrity and security is maintained across a database.