Security

SQL Server provides numerous levels of security. At the outermost layer, SQL Server logon security is integrated directly with Windows NT security, allowing a Windows NT server to authenticate users. With this Windows NT authentication in place, SQL Server can take advantage of the security features of Windows NT, such as password encryption, password aging, and maximum length restrictions on passwords.

Windows NT authentication relies on trusted connections, which make use of the impersonation feature of Windows NT. Through impersonation, SQL Server can take on the security context of the Windows NT user account initiating the connection and test whether the security identifier (SID) has a valid privilege level. Windows NT impersonation and trusted connections are available with any of the available network interfaces (Net-Libraries) when connecting to SQL Server running under Windows NT.

For SQL Server running under Windows 95 or Windows 98, Windows NT authentication isn't available. An administrator must create SQL Server login accounts within SQL Server. Any user connecting to SQL Server must supply a SQL Server login name and password, regardless of whether she has already logged on to the network. This type of validation is what's known as SQL Server authentication .

SQL Server can be installed in a mixed security model, which means that Windows NT_based clients can connect using Windows NT authentication, and connections that don't come from Windows NT clients , or that come across the Internet, can connect using SQL Server authentication. In addition, when connecting to an instance of SQL Server that has been installed with mixed security, a connection can always supply a SQL Server login name explicitly. This would allow a connection to be made using a login name distinct from the username in Windows NT. However, if you connect to an instance of SQL Server configured for only Windows NT authentication, you won't be able to supply a SQL Server logon name, and your Windows NT username determines your level of access to SQL Server.

Monitoring and Managing Security

SQL Server makes it easy to monitor logon successes and failures. Using SQL Server Enterprise Manager, administrators can simply check the appropriate box in the Security tab of the Properties dialog box for a particular installation of SQL Server. When logon monitoring is enabled in this way, each time a user successfully or unsuccessfully attempts to log on to SQL Server, a message is written to the Windows NT application log, the SQL Server error log, or both, indicating the time, date, and user who tried to log on.

SQL Server has a number of facilities for managing data security. Access privileges (select, insert, update, and delete) can be granted or denied to users or groups of users on objects such as tables and views. Execute privileges can be granted on local and extended stored procedures. For example, to prevent a user from directly updating a specific table, you can write a stored procedure that updates the table and then cascades those updates to other tables as necessary. You can grant the user access to execute the stored procedure, thereby ensuring that all updates will take place through the stored procedure, eliminating the possibility of integrity problems arising from ad hoc updates to the base table.