Prevent SELECT * query

Question

Hi all, Is there a way to prevent users from running SELECT * FROM TABLE, queries? I was poking around policies in sql 2008 couldn't find anything? Any help is appreciated.
Thanks

Answer 1

Hi,

Check this link

http://doc.ddart.net/mssql/sql70/ga-gz_4.htm

I hope this link might be helpful for you

---

Rajesh Jonnalagadda http://www.ggktech.com

Answer 2

You can add a dummy column to the table and DENY select permissions on that column.

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 3

There is no way in SQL Server to specifically prevent "SELECT * FROM TABLE". 

Idera make a product which is like a "SQL Proxy" server, which looks at every SQL command.

 http://www.idera.com/Products/SQL-Server/SQL-compliance-manager/

Answer 4

If you only have a specific set of users for whom you want to deny SELECT priveleges on a set of tables, then you can use the DENY option.
Eg:

DENY

 

SELECT ON OBJECT::table1 to tester

This is a bit cumbersome and might conflict with other operations if users need to SELECT from this table from a different application. But if that is not a criteria, then a DENY could work in your case.

---

This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL

Answer 5

If you only have a specific set of users for whom you want to deny SELECT priveleges on a set of tables, then you can use the DENY option.
Eg:

DENY

 

SELECT ON OBJECT::table1 to tester

This is a bit cumbersome and might conflict with other operations if users need to SELECT from this table from a different application. But if that is not a criteria, then a DENY could work in your case.

---

This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL

Amit,
The context of this thread is preventing "SELECT *" not select access in general.  Users should still be able to do "SELECT ColA, ColB ..."

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 6

There is an article on SQL Server Central today about this very topic:

http://www.sqlservercentral.com/articles/SELECT+*/68324/

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 7

There is an article on SQL Server Central today about this very topic:

http://www.sqlservercentral.com/articles/SELECT+*/68324/

---

Yep, and the discussion attached to that article is quite fun to read as well.

Preventing SELECT * also prevents COUNT(*), which can be bad because that forces a person to do COUNT([Column]) which is NOT the same as COUNT(*).  (That's not directed at you Jonathan...  ;) )

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 8

My bad... Got the context wrong :(

---

This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL

Answer 9

Hi Phil/Jonathan, I looked at that article, I dont think thats feasible in our environment, Is there any other alternative?
Thanks

Answer 10

Hi Phil/Jonathan, I looked at that article, I dont think thats feasible in our environment, Is there any other alternative?
Thanks

No, not really.  Why are you trying to prevent perfectly valid SQL syntax instead of enforcing good code reviews?

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 11

Hi Phil, We are enforcing code reviews, but we dont have control over every query run by users/developers and most of time we find one user or the other running a select * on a huge table taking lot of resources.

Answer 12

Hi Phil, We are enforcing code reviews, but we dont have control over every query run by users/developers and most of time we find one user or the other running a select * on a huge table taking lot of resources.

But preventing select * will not prevent consumption of resources.  In fact, it may simply frustrate users.

If you are on 2008, look at resource governor.

Personally, I don't like that you are trying to control one bad user from selecting against a huge table.  Is it a case where the user forgot a WHERE clause, or used one but that wasn't limiting enough?  It seems like a user education issue to me.  Preventing "SELECT *" has other negative downsides like preventing COUNT(*), so as far as I'm concerned preventing "SELECT *" should be avoided and other actions should be investigated like user education, resource governance, etc...

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 13

The solution to that particular problem is to not allow developers access to write SELECT statements.   If you require them to use a DBA or Database Analyst to write Stored Procs, for them, then you will have control over what commands are run.

Answer 14

Yep, and the discussion attached to that article is quite fun to read as well.

Preventing SELECT * also prevents COUNT(*), which can be bad because that forces a person to do COUNT([Column]) which is NOT the same as COUNT(*).  (That's not directed at you Jonathan...  ;) )

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

I'm at the PASS conference this week, so I actually didn't get to look at it to heavily or read the comments so thanks for pointing that out. 

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 15

I would also argue "SELECT * FROM TABLE" is not the real problem with this query.  The real problem, on large tables, is the lack of a WHERE clause.

The suggestions of creating a dummy column etc, do not address the real problem.  Idera's product claims to stop these queries.  However, it is extremely expensive.