The resultset grid shows all columns sent by the server, right? Not always! Under certain circumstances, Microsoft^® SQL Server^® transmits columns that weren’t referenced in the SELECT statement and that don’t show up in tools like SSMS.

How many columns does the below view return? Four seems like the obvious answer but it’s only sometimes correct. This view sometimes returns four columns—and other times returns nine!

CREATE VIEW dbo.EmployeeSummary AS
SELECT
  e.FirstName,
  e.LastName,
  dl1.MonthName + ' ' + dl1.DayString AS Birthday,
  dl2.YearString AS EmployedSince
FROM Employee e
  JOIN UserAccount u ON e.UserName = u.UserName
  JOIN EmploymentHistory h ON e.SSN = h.SSN
  JOIN DateLookup dl1 ON h.HireDate = dl1.Date
  JOIN DateLookup dl2 ON u.Birthdate = dl2.Date;

The extra five columns (shaded in yellow, below) are returned as hidden columns. They won’t appear in most query tools, yet the data they contain still crosses the wire and can be accessed programmatically on the client-side.

What’s going on?!

These hidden columns support browse mode, a relic from the legacy db-library.

Browse mode requires that the client know the primary key values associated with each row in the resultset. With browse mode, if the SELECT statement’s column list doesn’t include the primary key columns for all tables referenced in the query and the account executing the query has access to those tables, the missing key columns are added to the resultset as hidden columns.

Should you be concerned?

Strictly speaking, this is not a security vulnerability. It doesn’t allow unauthorized access to data. However, it can be used to access data in unexpected ways and may allow access to unexpected data.

Access to Data in Unexpected Ways

Let’s say you’re auditing access to privileged human resources tables but you’re not recording access to the above view because you think it shows only unprivileged data. A nefarious user with access to HR’s tables wants employee Social Security numbers but doesn’t want to raise audit alarms. If that user enables browse mode, they can execute a query against the view—which won’t ring any alarm bells—and—thanks to the data contained in the hidden columns—come away with the Social Security numbers. The user accessed data they had permission to access but did so in an unexpected way which allowed them to circumvent monitoring.

Access to Unexpected Data

Combine browse mode with a stored procedure that uses EXECUTE AS and the user may end up with access to data that the database administrator was unaware could be accessed.

Let’s say you adapt the example view into a stored procedure that uses EXECUTE AS. A user executes this procedure with browse mode turned on. Unlike the ‘Access to Data in Unexpected Ways’ scenario, this user has no access to the underlying tables. However, the fact that browse mode is turned on is passed to the query inside the stored procedure. Since this query executes in the context of an account that has access to the underlying tables (the EXECUTE AS account), the resultset it returns contains the same five hidden columns that accompanied the view!

CREATE PROCEDURE [dbo].[procBirthdays_ExecuteAsTest1]
  WITH EXECUTE AS 'PrivilegedUser' AS
SELECT
  e.FirstName,
  e.LastName,
  dl1.MonthName + ' ' + dl1.DayString AS Birthday,
  dl2.YearString AS EmployedSince
FROM Employee e
  JOIN UserAccount u ON e.UserName = u.UserName
  JOIN EmploymentHistory h ON e.SSN = h.SSN
  JOIN DateLookup dl1 ON h.HireDate = dl1.Date
  JOIN DateLookup dl2 ON u.Birthdate = dl2.Date;

Thanks to EXECUTE AS and browse mode, the user now has the ability to retrieve data that they otherwise would be unable to access—data that, almost certainly, the stored procedure author had no idea could be retrieved through the procedure.

Technically, this is ‘behavior as designed’ but my guess is that most database administrators and developers are unaware that this is how things are designed.

It’s also inconsistent with how browse mode is handled in the case of a view where a user with no access to the underlying tables is allowed to retrieve data thanks to ownership chaining. Apparently, ownership chaining doesn’t switch the context to execute as the owning user, so the hidden columns are not returned.

Conclusion

Whether browse mode is enabled is controlled by the client. There’s no server option to disable or prohibit it. If you work in the world of Microsoft SQL Server, it’s important to be aware of how this legacy mode can be used to access data in unexpected ways and, possibly, to access data you didn’t think could be accessed.

Related Microsoft Connect Item: 3137211—Suggests that Microsoft change how EXECUTE AS + browse mode works so that that scenario does not return hidden columns.

If you’d like to learn more about what takes place behind-the-scenes between SQL Server and clients, you may enjoy watching T-SQL on the Wire: What takes place between client and server?