Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Preventing usage of "SELECT *..."

By Raghuram (AJ), (first published: 2009/11/05)

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 
Total article views: 43989 | Views in the last 30 days: 7
 
Related Articles
FORUM

Not Selecting All Columns in select query

Not Selecting All Columns in select query

FORUM

Auto-Select Database Name

Auto-Select Database Name

FORUM

variable columns in select statements?

want to create select's with variable column names

FORUM

column into a row using select

column into a row using select

FORUM

sp_executesql dynamic columns in select statement

sp_executesql dynamic columns in select statement

Tags
deny select    
select *    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones