SQL Server Performance Forum – Threads Archive

Restricting select *

Is there is any procedure by which we can restrict select * from being executed on server.
I m wondering how can I accomplish this task.

---

I don’t think you can, U can only restrict specified columns

---

Dinesh, if some columns are restricted, then Select * wont work. Am I correct?
Madhivanan Failing to plan is Planning to fail

---

You can have change management and source code management processes. [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />] That’s really the only way to currently restrict it. You should have that already anyway though.<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.

---

Yes, I believe so

---

And, how is he going to restrict these columns? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

What I meant was he is can restrict a column user won#%92t be able to use select *, but that column has to be a idle column which is not used store any valid data.

---

Hi aaronsandy, to deal with this i feel view can provide better security.
U give users access only to view and not directly to the table.
So that users can access only those columns which will be there in the view.
Even in view you can specify top operator.

---

Derrick, if we deny a column to the user, select * from table wont work deny select (columnname) on tablename to username Am I correct? Madhivanan Failing to plan is Planning to fail

---

Yes, you can do that. He would have to either identify or create a column though on every single table in the server. There’s no way to globally deny it. Here is an example of the method you are describing. –Setup the test.
EXEC sp_addlogin ‘user1′,’user1′,’Northwind’
EXEC sp_grantdbaccess ‘user1′,’user1’
EXEC sp_addrolemember ‘db_owner’,’user1′
GO
CREATE TABLE testaccess(col1 int, col2 int) INSERT testaccess(col1, col2) VALUES(1,2)
GO –Login as user1 and run below:
SELECT * FROM testaccess –Now deny the select on a particular column.
DENY SELECT (col2) ON testaccess TO user1
GO –Switch back to user1 and run script this:
SELECT * FROM testaccess –Does not work.
SELECT col1 FROM testaccess –Works.
SELECT col2 FROM testaccess –Does not work.
GO –Clean up.
DROP TABLE testaccess
EXEC sp_droprolemember ‘db_owner’,’user1′
EXEC sp_revokedbaccess ‘user1’
EXEC sp_droplogin ‘user1’
GO MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

]]>