SQL Server Security Strategies

You can use SQL Server facilities to implement many useful data access security strategies:

• Remove the Guest Login   Drop the guest login from database access permissions.
• Identify Common Work Groups   Identify common work groups with common database permission requirements and create a group-based security profile.
• Provide an Anonymous Logon Account for the Domain   If IIS is on a stand-alone server and other remote resources will be accessed from that IIS machine, it is generally the best approach to make the anonymous logon account a domain account. This means that whenever authentication is required, the account will always be available to other network servers for validation. The most obvious advantage here is that it satisfies SQL Server's Named Pipes authentication.
• Use Standard Security for IIS Applications   If your IIS application uses SQL Server, use the standard security mode. This will give you maximum flexibility in deploying your application to network workstations.
• Use Views   Restrict data access by developing unique views that limit the amount of data a user can see or modify. For example, you could allow access to some columns of a table but restrict access to other columns containing sensitive data. Access to these views is regulated through SQL server permission schemes. Some corporate development sites prefer this approach because it provides the highest degree of security and is the easiest to implement.
• Use Stored Procedures   If data access must be very secure, consider using only stored procedures for all data viewing and modification. You can simplify the administration of data access permissions by granting EXECUTE permission to run certain stored procedures. This avoids assigning discrete permissions to all of the tables and views referenced within the stored procedure or embedded SQL statements. If you protect data access by using only stored procedures, remove all SELECT, UPDATE, INSERT, and DELETE privileges from every table and view in the database.
• Restrict Query Tools   If some users will be using query tools, consider assigning each user two logon accounts: a primary logon account for queries, and an application logon account for using your application. The primary query logon account should be their Windows NT user identification and restricted to READ ONLY. Thus, when the user runs the query tool, the query tool will receive the Windows NT logon account permissions and be limited to READ ONLY for the permitted databases and objects. The second logon account is for your application, where you control data access with the application's built-in methods and processes.
• Construct an Audit Trail   You can easily audit data access by creating triggers that automatically execute each time a table is modified. User permissions are not required to execute a trigger.