SQL really does not have anything that will do what you are asking but you could probably get close. As mentioned there is TDE but that is just meant to prevent someone from walking off with the physical database files or backups and using them on another server. The entire database is encrypted on the disk, however any login with rights to that db can login and view the decrypted data using any method they choose. Another option is cell level encryption. This will encrypt the data in a particular column and is useful for encrypting certain data within the database. Here the data in those columns will not be decrypted until they are actually used. The problem with cell level encryption though is that it does require code and schema changes to implement.
This is a nice technical paper that describes what is available in SQL 2008 and how each option can be used alone or together.