<div>
Main question is why do you let users to perform ad-hoc queries on a Production database?<br />
Access to Production databases should me made only through an application where bad code can be controlled.
</div>


<div>
&quot;Main question is why do you let users to perform ad-hoc queries on a Production database?&quot;<br />
<br />
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.<br />
<br />
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
</div>


<div>
can or can't sorry ? seems a bit confuse.
</div>


<div>
Vitor is correct in that there is not an automated process anywhere that will prevent developers from typing a 'SELECT * FROM' type query. &nbsp;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.
</div>


<div>
&quot;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. &quot;<br />
<br />
yeah, we are doing this too, but I am thinking about sth robust .. automated.. tks.
</div>


<div>
MohitPandit,<br />
<br />
&quot;auto code-review rule can be implement and run<br />
&quot;<br />
<br />
by system? or manual ?<br />
<br />
&quot;Apart, in case really needed all column then we can get from sys.columns at run time and execute query with sp_executesql&quot;<br />
<br />
what is that for? &nbsp;I don't get it .
</div>


<div>
<i>it seems not working much at all and I also agree on the comment post there.</i><br />
Let's back up a bit. &nbsp;Why do you say that Aaron's solution &quot;not working much at all&quot; &nbsp;Have you really tried it or do you not agree with the approach? &nbsp;Those are two totally different questions.
</div>


<div>
Anthony,<br />
<br />
&quot;Let's back up a bit. &nbsp;Why do you say that Aaron's solution &quot;not working much at all&quot; &quot;<br />
<br />
which Aaron ? that post is not from Aaron, right? <br />
<br />
&quot;Have you really tried it or do you not agree with the approach? &nbsp;Those are two totally different questions. &quot;<br />
<br />
did you read the comment on that link ? people there BASICALLY share the same through. &nbsp;very hard to apply, sometimes debugs seems to do sth like Select top 1 * e.g. quick enough. <br />
<br />
I understand code review can control that but basically I am hoping some system configuration and automate that.
</div>


<div>
<i>which Aaron ? that post is not from Aaron, right? </i><br />
Aaron Bertrand <br />
<i>did you read the comment on that link ?</i><br />
Absolutely. &nbsp;Did you? &nbsp;I ask because you had no clue who was Aaron. &nbsp;Hint: He was the author of the solution in that article,<br />
<br />
In any case, I think you answered my two questions: <br />
You did not test it, <br />
You did not agree with the approach.<br />
<br />
Anyone else reading this thread, the solution in the link does indeed work except for the edge case:
<blockquote>
if<br />
SET ARITHABORT OFF<br />
SET ANSI_WARNINGS OFF<br />
<br />
division by zero “fails” and select * is executed with only warning triggered.
</blockquote>
</div>


<div>
&quot;which Aaron ? that post is not from Aaron, right? <br />
&nbsp;Aaron Bertrand &quot;<br />
<br />
forget about that, the author just say attend his session. I am focusing on the link by Klaus Aschenbrenner.<br />
<br />
there are only one say yes, but most of the rest disagree.
</div>


<div>
<blockquote>
I am focusing on the link by Klaus Aschenbrenner.
</blockquote>
That's a nice trick and good as an academic solution but let be honest, it's hard to manage in the real world.<br />
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.
</div>


<div>
&quot;That's a nice trick and good as an academic solution but let be honest, it's hard to manage in the real world.&quot;<br />
<br />
yeah, I can see what anyone else said,, it is hard, true. so relies on manual code review. <br />
<br />
&quot;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.&quot;<br />
<br />
indeed.<br />
<br />
&quot;Also you'll need to handle that error in your application or it can terminate abruptly. &quot;<br />
<br />
yes yes yes..
</div>


<div>
Sorry, for late response.<br />
<br />
We can have auto code-review using SQL Server Database Project in Visual Studio.<br />
<br />
Please find attached snap shot file here.<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/02_w08/1082035/Auto_DB_Code_Review_v1.0.png" target="_blank" class="file-inline" title="Auto_DB_Code_Review_v1.0.png (33 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Auto_DB_Code_Review_v1.0.png</a>
</div>


Auto_DB_Code_Review_v1.0.png

<div>
very good sir, any steps by steps on how to setup it up ?
</div>


<div>
<i>forget about that, the author just say attend his session. I am focusing on the link by Klaus Aschenbrenner.</i><br />
Do you comprehend English? &nbsp;Aaron Bertrand is the one that created the solution. &nbsp;Again you may not like it, but it does work. &nbsp;Period.
</div>


<div>
Vitor,<br />
<br />
<i>That's a nice trick and good as an academic solution but let be honest, it's hard to manage in the real world.</i><br />
And I do not disagree with you. &nbsp;The point that I was making was the author specifically stated &quot;it seems not working much at all&quot;, &nbsp;that is clearly wrong &nbsp;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.
</div>


<div>
I'll prepare a document for step-by-step and let you update here.
</div>


<div>
Anthony,<br />
<br />
&quot;Do you comprehend English? &nbsp;Aaron Bertrand is the one that created the solution. &nbsp;Again you may not like it, but it does work. &nbsp;Period. &quot;<br />
<br />
I only focus on that article and the comment down the page is valuable, I am not focus on WHO WRITE that.<br />
<br />
I am not here to talk about person, there are nothing personal here.<br />
<br />
&quot;even if they had no clue that Aaron Bertrand is one of the leading SQL Server gurus. &quot;<br />
<br />
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.<br />
<br />
they like it or not is up to them, but I understand them.. we are using similar effort here. human eyes to check.<br />
<br />
MohitPandit,<br />
<br />
tks. you are doing great. just like Victor, I see helpful sense.
</div>


<div>
<div class="content wysiwyg-content">
hi all,<br />
<br />
&nbsp;I read this one how to prevent user from doing select * from table: <a href="http://www.sqlpassion.at/archive/2015/10/26/how-to-prevent-select-statements/#comment-54313" rel="ugc">http://www.sqlpassion.at/archive/2015/10/26/how-to-prevent-select-statements/#comment-54313</a><br />
<br />
it seems not working much at all and I also agree on the comment post there.<br />
<br />
how you guys prevent this if any?
</div>
</div>


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?

How to prevent from select * from statement.

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.