Encrypted text in Profiler for unencrypted modules

A colleague came up with this situation:

************

I am tracing some SQL activity on a SQL 2008 R2, Enterprise SP2 64-bit server. In the profiler trace, I keep seeing Encrypted Text in the textdata column for procedures that are clearly plain text and are not encrypted. Though, I do notice in certain scenarios, where the parent object is an encrypted stored procedure that is calling unencrypted SP’s/triggers within it and the text from those SP’s are also getting encrypted by default. Is that how the behavior must be like?

************

Indeed, that’s the way it is expected to work. If you have access to SQL Server’s source code, all that logic pivots around CTrcAutoHideEncryptedText. That class is used in CMsqlExecContext::FExecute to automatically set/reset Encryption flag on CTraceData. When we first encounter an encrypted proc, we call SetEncryptedFlag() to indicate we are within the range of an encrypted proc. This switches the status on TraceData (m_fEncrypted), and this object holds pointer to TraceData so that it can reset the flag during destructor. Any additional encrypted procs inside this range do nothing because we are already inside. If any exception occurs, destructor will guarantee clean reset of TraceData::m_fEncrypted.

I don’t know of any options other than removing the ENCRYPTION clause off all those procedures invoking directly or indirectly whatever statements you want to profile.