A few weeks ago I wrote about the comparison of SELECT 1 and SELECT * with use in EXISTS T-SQL statements.  The subject has been discussed plenty of times but with any subject, revisiting the topic and reasons we write things the way we do, is still valuable.  Something did come up offline regarding the concept of how SELECT * will expand the complete column list metadata and security as it pertains to column level grants.  A friend, guy who was an MVP for years and still one in my eyes and all around awesome SQL guru, Laerte Junior (B | T) pointed out that the SELECT 1 examples would still fail based on a user only having SELECT on one or more columns but not all the columns in the table.

This indicated that the metadata was indeed being examined.  What I found is, it seems the security model, being the first thing SQL Server will account for, is evaluated prior to the full examination of the metadata on the columns.  The article on the comparison still holds some weight on why or why not to use one method over the other.  However, this was absolutely a great thing to bring up as many implementations of SQL Server databases have taken full advantage of column level security grants.

Example

The following example will go over what Laerte Junior pointed out.

USE master
GO
CREATE LOGIN colUser WITH PASSWORD=N'passmein'
GO
USE AdventureWorks
GO
CREATE TABLE tblColPerms (id int,col1 varchar(10),col2 varchar(10))
GO
CREATE USER colUser FOR LOGIN colUser;
GO
GRANT SELECT ON tblColPerms(col2) TO colUser
GO
--Open a new connection with SSMS using colUser

In the new query window using the context of coluser

USE AdventureWorks
GO
BEGIN TRY
SELECT col2 FROM tblColPerms
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH;

BEGIN TRY
SELECT col1 FROM tblColPerms
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH;

BEGIN TRY
SELECT 1 FROM tblColPerms
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH;

BEGIN TRY
SELECT col2 FROM tblColPerms WHERE exists (SELECT 1 FROM tblColPerms)
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH;

The first select statement will function correctly. The next statements will result in errors regarding the security level the colUser has to examine all the columns in the table based on the user of SELECT 1 or SELECT on a column the colUser does not have access to.

(0 row(s) affected)

The SELECT permission was denied on the column ‘col1’ of the object ‘tblColPerms’, database ‘AdventureWorks’, schema ‘dbo’.

The SELECT permission was denied on the column ‘id’ of the object ‘tblColPerms’, database ‘AdventureWorks’, schema ‘dbo’.

The SELECT permission was denied on the column ‘id’ of the object ‘tblColPerms’, database ‘AdventureWorks’, schema ‘dbo’.

Summary

This was a great tip and catch by Laerte Junior.  It also shows that the code we write, although it may be efficient or meeting best practices, can have an impact on the complete cycle of a transaction as it flows through SQL Server.