Preventing usage of "SELECT *..."

We know the possible pitfalls of "SELECT *..." in programmable objects and often DBAs discourage users or developers from using it in their queries. However it's hard to enforce that in code. Here's one way to make sure users do not use "SELECT * FROM..."

If you create a dummy column in the table and deny SELECT permissions on this new column to the user, this user can select the rest of the columns but cannot perform a "SELECT * FROM..." on that table. Let's try it out...

Let's first create a new login, database & database user

USE MASTER
GO 
CREATE LOGIN test_user WITH PASSWORD = 'test_user_pwd';
GO 
CREATE DATABASE DenySelectStar;
GO 
USE DenySelectStar
GO
CREATE USER test_user FROM LOGIN test_user WITH DEFAULT_SCHEMA = DenySelectStar;
GO 
EXEC sp_addrolemember N'db_datareader', N'test_user'
GO 

Next we create a new table with the dummy column, named "dummycolumn". We insert a few rows and deny select on this dummycolumn to test_user.

CREATE TABLE dbo.Table_1(
  IdentityKey INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
  ,ColumnOne INT NULL
  ,DummyColumn CHAR(1) NULL
);
GO 

INSERT INTO dbo.Table_1 (ColumnOne) 
  SELECT 1
    UNION
  SELECT 2
    UNION
  SELECT 3
    UNION
  SELECT 4
    UNION
  SELECT 5;
GO 

DENY SELECT ON OBJECT:: dbo.Table_1(DummyColumn) TO test_user;
GO

Now open a new query window and login using the newly created test_user and try the following queries...

USE DenySelectStar
GO 
SELECT * FROM dbo.Table_1;

--Result
Msg 230, Level 14, State 1, Line 1
The SELECT permission was denied on the column 'DummyColumn' of the object 'Table_1", database 'DenySelectStar', schema 'dbo'.

As you see above results, SELECT * was not allowed. The following query is the recommended way to select columns:

USE DenySelectStar
GO 
SELECT IdentityKey, ColumnOne FROM dbo.Table_1;

--Result 
IdentityKey ColumnOne
----------- -----------
1           1
2           2
3           3
4           4
5           5 

(5 row(s) affected)

There is however a catch (ah...I knew it!?). There are a few things that this user cannot do such as: COUNT(*), COUNT(1) or even SELECT 1 from this table. But there is a way around it. Replacing the * or 1 with the primary key on that table gives the desired results. Let's see the examples...

USE DenySelectStar
GO
SELECT COUNT(*) FROM dbo.Table_1;
SELECT COUNT(1) FROM dbo.Table_1;

--Result 
Msg 230, Level 14, State 1, Line 1
The SELECT permission was denied on the column 'DummyColumn' of the object 'Table_1", database 'DenySelectStar', schema 'dbo'.
Msg 230, Level 14, State 1, Line 2
The SELECT permission was denied on the column 'DummyColumn' of the object 'Table_1", database 'DenySelectStar', schema 'dbo'.

USE DenySelectStar
GO
SELECT COUNT(IdentityKey) As TotalRows FROM dbo.Table_1;

--Result
TotalRows
-----------
5 

(1 row(s) affected)


USE DenySelectStar
GO
IF EXISTS (SELECT * FROM dbo.Table_1)
 PRINT 'Has Rows'
IF EXISTS (SELECT 1 FROM dbo.Table_1)
 PRINT 'Has Rows'

--Result 
Msg 230, Level 14, State 1, Line 1
The SELECT permission was denied on the column 'DummyColumn' of the object 'Table_1", database 'DenySelectStar', schema 'dbo'.
Msg 230, Level 14, State 1, Line 3
The SELECT permission was denied on the column 'DummyColumn' of the object 'Table_1", database 'DenySelectStar', schema 'dbo'.


USE DenySelectStar
GO
IF EXISTS (SELECT IdentityKey FROM dbo.Table_1)
 PRINT 'Has Rows'

--Result
Has Rows

I agree it seems too much work to make sure that the users don't use SELECT *, but if you really want to enforce it, here's one way to go about it.

Here is the cleanup for your database that you can run:

USE MASTER 
GO
DROP DATABASE DenySelectStar;
GO
DROP LOGIN test_user;
GO