Preventing the usage of SELECT *

One of my friends asked “Is it possible to prevent users from using SELECT * from a table in the queries?” Well. The one way I can think of is to create a view that has all the columns from the table and add an extra column as an invalid derived expression.

Let us consider the following table

create table employee
(
emp_id char(10),
emp_name varchar(100),
dob datetime,
address varchar(100),
phone varchar(20)
)

insert into employee(emp_id,emp_name,dob,address,phone)
select 'EMP0000001','Madhivanan','20000101','Chennai,India','+91299200200'

You can now use * in the SELECT statement

select * from employee

To prevent this create the following view

create view emp_view
as
select *,cast('@@@' as datetime) as error_col from employee

Now you can not use SELECT * on this view

select * from emp_view

The above results to

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

So you need to explicitly type out the required column names to get the data.

select emp_id,emp_name,dob,address,phone from emp_view

The result of the above is

emp_id emp_name dob address phone
—————- ————– —————————————– ——————– ———-
EMP0000001 Madhivanan 20000101 00:00:00.000 Chennai,India +91299200200

You need to also DENY SELECT permission to the users on that table and GRANT SELECT permission to the view

If you know any other methods, post it in the comment section

Advertisements

6 thoughts on “Preventing the usage of SELECT *

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s