I believe TDE really only protects against the smash-and-grab risk. People with SELECT permission on the table can still access the data - it's transparent to them. If you want to store it encrypted, start here: http://technet.microsoft.com/en-us/library/bb964742%28v=sql.100%29.aspx TDE also requires Enterprise Edition, which not everyone has the luxury of working with.

In a nutshell, you'll need to create keys and then encrypt the data using the keys. A simple word of advice if you decide to go this route is to backup your keys. There are commands to do this referenced in the above page, but I prefer to also keep the SQL statements I use to originally create the key. This way, I know I can recreate the key when needed. I was burned by this once and I vowed it wouldn't happen to me again.