How to prevent from select * from statement.

hi all,

 I read this one how to prevent user from doing select * from table: http://www.sqlpassion.at/archive/2015/10/26/how-to-prevent-select-statements/#comment-54313

it seems not working much at all and I also agree on the comment post there.

how you guys prevent this if any?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MohitPanditCommented:
In case, you want to prevent only then things like

1. Training session for development team, not to use SELECT *
2. Code-Review team like database project can be build and auto code-review rule can be implement and run

Apart, in case really needed all column then we can get from sys.columns at run time and execute query with sp_executesql

Let me know, what are your thoughts?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Main question is why do you let users to perform ad-hoc queries on a Production database?
Access to Production databases should me made only through an application where bad code can be controlled.
marrowyungSenior Technical architecture (Data)Author Commented:
"Main question is why do you let users to perform ad-hoc queries on a Production database?"

nono. I didn't say this.. I just want to control it everywhere if possible. it is a hope. you know resource governor , it only control CPU and RAM, none of them control TempdB resource, we need to program that up.

our code will be review but controlling this means for any one , by some reason, or planned, can't do this. we are controlling much better
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Vitor MontalvãoMSSQL Senior EngineerCommented:
No, you can't control things like if 'SELECT *' then throw an error or warning.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
can or can't sorry ? seems a bit confuse.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can not.
marrowyungSenior Technical architecture (Data)Author Commented:
ok, can't anyway.
Jim HornSQL Server Data DudeCommented:
Vitor is correct in that there is not an automated process anywhere that will prevent developers from typing a 'SELECT * FROM' type query.  In most businesses there is some form of developer review either by a lead or an architect to review code for accuracy and syntax, and this would be a common thing to review.
marrowyungSenior Technical architecture (Data)Author Commented:
"In most businesses there is some form of developer review either by a lead or an architect to review code for accuracy and syntax, and this would be a common thing to review. "

yeah, we are doing this too, but I am thinking about sth robust .. automated.. tks.
marrowyungSenior Technical architecture (Data)Author Commented:
MohitPandit,

"auto code-review rule can be implement and run
"

by system? or manual ?

"Apart, in case really needed all column then we can get from sys.columns at run time and execute query with sp_executesql"

what is that for?  I don't get it .
Anthony PerkinsCommented:
it seems not working much at all and I also agree on the comment post there.
Let's back up a bit.  Why do you say that Aaron's solution "not working much at all"  Have you really tried it or do you not agree with the approach?  Those are two totally different questions.
marrowyungSenior Technical architecture (Data)Author Commented:
Anthony,

"Let's back up a bit.  Why do you say that Aaron's solution "not working much at all" "

which Aaron ? that post is not from Aaron, right?

"Have you really tried it or do you not agree with the approach?  Those are two totally different questions. "

did you read the comment on that link ? people there BASICALLY share the same through.  very hard to apply, sometimes debugs seems to do sth like Select top 1 * e.g. quick enough.

I understand code review can control that but basically I am hoping some system configuration and automate that.
Anthony PerkinsCommented:
which Aaron ? that post is not from Aaron, right?
Aaron Bertrand
did you read the comment on that link ?
Absolutely.  Did you?  I ask because you had no clue who was Aaron.  Hint: He was the author of the solution in that article,

In any case, I think you answered my two questions:
You did not test it,
You did not agree with the approach.

Anyone else reading this thread, the solution in the link does indeed work except for the edge case:
if
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

division by zero “fails” and select * is executed with only warning triggered.
marrowyungSenior Technical architecture (Data)Author Commented:
"which Aaron ? that post is not from Aaron, right?
 Aaron Bertrand "

forget about that, the author just say attend his session. I am focusing on the link by Klaus Aschenbrenner.

there are only one say yes, but most of the rest disagree.
Vitor MontalvãoMSSQL Senior EngineerCommented:
I am focusing on the link by Klaus Aschenbrenner.
That's a nice trick and good as an academic solution but let be honest, it's hard to manage in the real world.
You'll need to had a column in every single table that you have and don't forget to add it in future new tables. Also you'll need to handle that error in your application or it can terminate abruptly.
marrowyungSenior Technical architecture (Data)Author Commented:
"That's a nice trick and good as an academic solution but let be honest, it's hard to manage in the real world."

yeah, I can see what anyone else said,, it is hard, true. so relies on manual code review.

"You'll need to had a column in every single table that you have and don't forget to add it in future new tables."

indeed.

"Also you'll need to handle that error in your application or it can terminate abruptly. "

yes yes yes..
MohitPanditCommented:
Sorry, for late response.

We can have auto code-review using SQL Server Database Project in Visual Studio.

Please find attached snap shot file here.
Auto_DB_Code_Review_v1.0.png
marrowyungSenior Technical architecture (Data)Author Commented:
very good sir, any steps by steps on how to setup it up ?
Anthony PerkinsCommented:
forget about that, the author just say attend his session. I am focusing on the link by Klaus Aschenbrenner.
Do you comprehend English?  Aaron Bertrand is the one that created the solution.  Again you may not like it, but it does work.  Period.
Anthony PerkinsCommented:
Vitor,

That's a nice trick and good as an academic solution but let be honest, it's hard to manage in the real world.
And I do not disagree with you.  The point that I was making was the author specifically stated "it seems not working much at all",  that is clearly wrong  and rather sad when a quick 5 minute test would have confirmed otherwise, even if they had no clue that Aaron Bertrand is one of the leading SQL Server gurus.
MohitPanditCommented:
I'll prepare a document for step-by-step and let you update here.
marrowyungSenior Technical architecture (Data)Author Commented:
Anthony,

"Do you comprehend English?  Aaron Bertrand is the one that created the solution.  Again you may not like it, but it does work.  Period. "

I only focus on that article and the comment down the page is valuable, I am not focus on WHO WRITE that.

I am not here to talk about person, there are nothing personal here.

"even if they had no clue that Aaron Bertrand is one of the leading SQL Server gurus. "

that's why you are so nerves on that. I respect anyone here who even can't help at all. I just like contribute and share ideas.. the object of EE. the reason why I come here.

they like it or not is up to them, but I understand them.. we are using similar effort here. human eyes to check.

MohitPandit,

tks. you are doing great. just like Victor, I see helpful sense.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.