Whenever you're developing a new application that hits SQL Server, make sure you design in security from the outset. In the new releae of the Community Tehcnal Preview (CTP) for SQL Server 2008, you'll notice that there are several new improvements in security. Even the documentation has been improved, moving the security topics to their own area. In fact, all the documentation has been refactored, conforming to the new Unified Content Model (UCM) that is slowly being adopted by all of the User Education (UE) groups at Microsoft. This will help you find the same layout of information (like security) no matter what the sever product.
The best way to design in security is to create a "Threat Model" which details the way someone could hack your app. More here: http://www.microsoft.com/mspress/books/6892.aspx. I use an "outside-in" approach, starting with the hardware and architecture and moving down through the stack to the database objects such as stored procedures and assemblies, even the data structure. In a distributed application, everything is a target.
If you bake the security concepts into your app at the outset, you're more likely to create a secure app. If you wait until you're actually coding to think about it, chances are you'll be tempted to take shortcuts that will bite you later.