A view is a powerful tool in SQL Server to simplify privilege configurations, isolate schema to developers, and tailor accessible data columns to individual users. However if a view is used in sub-queries, sometimes you may get unexpected results.

The following code is to check whether the SID of the current database owner is a valid server principal:

IF NOT EXISTS(SELECT 1 FROMsys.server_principals b 
 WHERE b.sid=(SELECT [sid] FROM sys.databases a WHERE a.name=DB_NAME()))
 BEGIN
       PRINT N'Invalid Database Owner'
 END
 ELSE
 BEGIN
       PRINT N'Valid Database Owner'
END

Run the query in SSMS, it succeeds with no error messages. However, actually the query has a problem. If you run the sub-query alone:

SELECT [sid] FROMsys.databases a WHERE a.name=DB_NAME())

 
Msg 207, Level 16, State 1, Line 1
Invalid column name 'sid'

The catalog view sys.databases does not have the column sid, it is called owner_sid in the catalog view. But SQL Server does not discover the issue in the first query. If we change [sid] to [any_col] in the first query, we will get the same error as previous one:

IF NOT EXISTS(SELECT 1 FROMsys.server_principals b WHERE b.sid=(SELECT [any_col] FROM
    sys.databases a WHERE a.name=DB_NAME()))
 BEGIN
      PRINT N'Invalid Database Owner'
 END
 ELSE
 BEGIN
       PRINT N'Valid Database Owner'
END

Msg 207, Level 16, State 1, Line 1
Invalid column name 'any_col'

Check the view definition:

SELECT OBJECT_DEFINITION(object_id('sys.databases'))

We get:

CREATE VIEW sys.databasesAS
       SELECT d.name, d.id AS database_id,
            r.indepid AS source_database_id,
            d.sid AS owner_sid,
             d.modified AS create_date,
 ...
FROM master.sys.sysdbreg d OUTER APPLY...

The sid is a column in the base table. If a column from a base table is renamed in the view definition, and the base column name is used in a sub-query against the view, SQL Server cannot discover the problem and it looks like it gets data from the base column.

This behavior is not just for system views. User defined views have the same behavior.

USE AdventureWorks
GO
CREATE VIEW Production.TestProduct AS
SELECT B.[Name] AS CatName,A.Name AS ProdName, C.Name AS SubCatName
FROM Production.Product A,Production.ProductCategory B,Production.ProductSubcategory C
 WHERE A.ProductSubcategoryID=C.ProductSubcategoryID 
 AND C.ProductCategoryID=B.ProductCategoryID
GO

Run the following queries, they all return right results. It looks that SQL Server "knows" from which base tables [Name] column to get data:

 
SELECT * FROM Production.ProductCategory  
  WHERE [Name] IN (SELECT [Name] FROM Production.TestProduct)
SELECT * FROM Production.Product 
  WHERE [Name] IN(SELECT [Name] FROM Production.TestProduct)
SELECT * FROM Production.ProductSubcategory 
  WHERE [Name] IN (SELECT [Name] FROM Production.TestProduct)

So it seems not too bad at all. However let’s change the view a little bit:

ALTER VIEW Production.TestProduct AS
 SELECT B.[Name] AS CatName,A.Name AS Prodname, C.Name AS SubCatName
 FROM Production.Product A,Production.ProductCategory B,Production.ProductSubcategory C
 WHERE A.ProductSubcategoryID=C.ProductSubcategoryID 
 AND C.ProductCategoryID=B.ProductCategoryID
 AND B.ProductCategoryID>1

The category with ProductCategoryID=1 is not included in the view. Run the following query, you will see all categories still returned:

SELECT * FROM Production.ProductCategory 
   WHERE [Name] IN (SELECT [Name] FROM Production.TestProduct)

So actually, SQL Server just discarded the sub-query that contains the invalid column name in the view. If we make a mistake in the sub-query using the base table column instead of the column name in the view, SQL Server processes the query as if the sub-query does not exist without giving any error messages. This is true too in the first query about sys.databases view. The query can never find the database with an invalid sid because SQL Server handles it the same as:

 IF NOT EXISTS(SELECT 1 FROMsys.server_principals b)
 BEGIN
       PRINT N'Invalid Database Owner'
 END
 ELSE
 BEGIN
       PRINT N'Valid Database Owner'
END

This behavior sounds a bug to me. SQL Server should report that the column does not exist in the view regardless it is used in a sub-query or not. Yes, if we do not misuse the base table column in the view, the problem can never happen. But we are human and easy to make this kind of mistakes, which will be hard to figure out.

The problem applies to both SQL Server 2005 [tested on SP2, Developer Edition] and 2000 [tested on SP4, Developer Edition].