Monday, February 14, 2011

Deny User to use "Select *" on SQL Table

As we all knows that if we use "Select *" in any query it reduce performance and its not a good practice. this is not best practice to use "select *". user/developer must pass field's name instead of using "*".
as normally every developer takes care about it but even though there is possibility to use it in queries.
so if you wants that no query with "select *" should work then you can do it using just single statement.

add a column to table on which you don't want "select *" to be run.
alter table tablename add column 
 DummyColumn CHAR(1) NULL

now deny rights to run "select" our newly added column named "dummycolumn" for user.

DENY SELECT ON OBJECT:: dbo.yourTableName(DummyColumn) TO your_user;

now if you will try to run
Select * from tableName
you will encounter this error:
The SELECT permission was denied on the column 'DummyColumn' of the object 'YourTableName", database 'YourDatabaseName', schema 'dbo'.

The only way to use this table is by passing column names with select like
select firstcolumnName, secondecolumnName from yourTableName

There are a few things that this user cannot do such as: COUNT(*), COUNT(1) or even SELECT 1 from this table. But there is a way around it. Replacing the * or 1 with the primary key on that table gives the desired results.

I agree it seems too much work to make sure that the users don't use SELECT *, but if you really want to enforce it, here's one way to go about it. But if you know better then please suggest me..

Solution By: Rajesh Rolen.

Share This!

No comments:

Powered By Blogger · Designed By Seo Blogger Templates