Question: How to STOP the Usage of SELECT * For Views?
Answer: Last week was a very interesting week, during the recent Comprehensive Database Performance Health Check, we identified a query which was running very slow for our customer.
After careful investigation, we figured out that the customer was using a view which had many columns in it. One of the developers had written a query over that view and again he was retrieving many other columns using further SELECT *.
A little bit later we fixed the performance issue by removing unnecessary columns from the query which were written poorly with SELECT *. Once the query started to work fine, the developer asked me the following question:
“Is there any way to stop the users from using “SELECT *” on a view? I want users to explicitly type out the column names. Is it even possible?”
Well. As you know “SELECT *” is one of the bad practices that should be avoided. There may be many solutions to stop usage of “SELECT *” but one easy solution I know is to make use of VIEW.
Let us create the following table.
USE tempdb GO CREATE TABLE customer_master ( customer_id INT, customer_name VARCHAR(100), dob DATETIME );
INSERT INTO customer_master (customer_id,customer_name,dob) SELECT 10001,'Johnson','1992-10-19' UNION ALL SELECT 10002,'Peter','1957-03-22' UNION ALL SELECT 10003,'Clara','1981-12-15' GO
Now create a view with an additional derived column which will throw an error
CREATE VIEW customer_master_view AS SELECT customer_id,customer_name,dob, <span style="color: #ff0000;">'a'+100 AS error</span> FROM customer_master
Note that the expression ‘a’+100 is not valid and will throw an error
Now if you apply SELECT * on this view
SELECT * FROM customer_master_view;
You will get the following error
Msg 245, Level 16, State 1, Line 185
Conversion failed when converting the varchar value ‘a’ to data type int.
So the only way to query this view is to explicitly type out the column names and exclude derived column
SELECT customer_id,customer_name,dob FROM customer_master_view;
Now you will get the required results.
I believe it is a very neat trick which worked out great in this case. If you have any alternative trick, please a comment and I will be happy to publish the same with due credit to you.
Important note: In order to make this possible, Database Administrator should GRANT SELECT permission on the VIEW and not on the table.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Interesting. Wonder if you could do the same thing with a calculated column?
You could create a BIT column and deny access to it i guess?
Wondering how it will work if the user has select permission on view and the view calls a scalar function but user doesn’t have execute access on function. Will the explicit type out of columns will work on view ?