Security Tips

When designing a database in the SQL Server environment, which will be accessed by public sources, here are a few guidelines to keep in mind:

Take advantage of the Guest default login. The Guest login is a special login defined by default in SQL Server. Guest has no rights to any object in the server aside from the ability to login. The Guest login can be granted read
access to the specific views, tables, or objects needed for your Web site. The Web site can then use this account when attaching to SQL Server objects, minimizing your site's exposure.
Use stored procedures where possible for updates and insertions. Coding static SQL in your ASP page is perhaps the most straightforward way to provide dynamic data content. However, keep in mind that not only can your Web site see this code, but so might a really clever hacker. To minimize this exposure it is often better to call a stored procedure, which in turn performs the required updates or inserts. A stored procedure can be granted permissions to access and modify objects and data that the ID calling the stored procedure cannot. This provides a shield between your site and the actual data being changed.
When tables are scanned for data, views can serve as security mechanisms. Through a view, users can query only the data provided by the view. The rest of the database is neither visible nor accessible. Permission to access the subset of data in a view must be granted or revoked, regardless of the set of permissions in force on the view’s underlying tables. Data in an underlying table that is not included in the view is hidden from users who are authorized to access the view but not the underlying table.

Don't expose more then you have to. In most cases, you will want to retain the majority of your data behind the 'firewall', and supply a mechanism to access this data. Always keep in mind that the any security can be compromised and, as such, preventative measures must be in place that assume that this will occur. One recommendation is to store only high-volume transactional data on the SQL Server that is directly accessed from a Web site. Supportive information can be maintained on a separate secured system, and retrieved as needed using Remote Stored Procedures, or other similar technologies.