Let's say we have some data that is encrypted and we would like to find out what key was used to perform the encryption. SQL Server 2005 knows what key was used to encrypt the data because the key identifier (the key_guid value) is prefixed to the encrypted data. We can find out the key same as SQL Server does with a one-line TSQL statement. Here's a small demo that wraps that one-line statement in a function for easier use:

-- Set up a database for this demo
--
create database test;

use test;

-- We use the identity_value clause to obtain the same fixed GUID on all systems
--
create symmetric key skey with algorithm = triple_des, identity_value = 'Test' encryption by password = 'Anss$pt@Ihnbwef&o!';

-- The following query should return
-- 5D910600-5D5F-874F-54F5-1892D884C477
--
select key_guid from sys.symmetric_keys where name = 'skey';

-- create a simple function to return the guid of the key that was used to encrypt a piece of data
--
create function key_guid_from_bytes (@data varbinary(8000))
returns uniqueidentifier
as
begin
   return convert(uniqueidentifier, @data);
end;

-- encrypt using skey and use the key_guid_from_bytes function to examine the encrypted data
-- we should retrieve the key guid - 5D910600-5D5F-874F-54F5-1892D884C477 and the key name - skey
--
open symmetric key skey decryption by password = 'Anss$pt@Ihnbwef&o!';

select dbo.key_guid_from_bytes(encryptbykey(key_guid('skey'), 'Top Secret'));
select name from sys.symmetric_keys where key_guid = dbo.key_guid_from_bytes(encryptbykey(key_guid('skey'), 'Top Secret'));

close symmetric key skey;

-- Cleanup
--
use master;
drop database test;