Do SQL Server Compatibility Levels Introduce Old Security Vulnerabilities

Question

Can anyone help settle what I thought was a simple question.

A fellow DBA friend and I were talking about compatibility levels in general, and their relation to security specifically. He posited that a good reason to bring them as current as possible was to reduce security vulnerabilities caused by them. I countered that formerly SPs and currently CUs and hotfixes would surely patch those vulnerabilities, i.e. running a database in 2005 compatibility mode in a 2014 SQL Server instance surely does not revert that database back to 2005 SP4 security levels/vulnerabilities.

I've been searching for documentation to back this claim up but I've been unable to find any. Does anyone know of any authoritative documentation that would show me either correct or in error?

Answer 1

It does not introduce past vulnerabilities, and applies mostly to T-SQL syntax and T-SQL features. As the documentation states,

Compatibility level affects behaviors only for the specified database, not for the entire server. Compatibility level provides only partial backward compatibility with earlier versions of SQL Server.

Later on the documentation goes on to list the differences between each mode and you'll see no mention of security or hotfixes, just features/behaviors that will be differently implemented or unavailable.

Exploits would generally be at the server and DB engine level, so that isn't really a concern here. It would be a pretty huge security risk if I always kept my MSSQL instances patched but had one database in an older mode that was never able to take advantage of my diligent patching efforts. Although that would be a great way to get people onto the newest version!

You could argue that using old T-SQL code constrained by old version syntax might cause one to use work-arounds to inelegantly achieve certain requirements, which in turn might cause code to be more at risk for having vulnerabilities, but that onus is on the developer and nothing inherent to MSSQL. I can't even think of an example where this applies, but I'm sure they exist.