Exclude a column from SELECT * within a View

Question

Exclude a column from SELECT * within a View

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

BH-428850 (1/12/2010)
Yes, I knew what the problem is and that is has to do with duplicate columns not being unique. I figured I could use dynamic sql in order to complete the select I want with a stored procedure but since I’m using this for reporting purposes I need it work within a view. Using dynamic sql to create the view might be a challenge as I would need to continually run some sort of a job to rebuild the view based on when a client adds or changes a user defined column in one of the tables.
It looks like I’ll have to chalk this one up as not being possible within a view.

Or, since this is SQL Server 2005, a DDL trigger on the three tables that dynamically rebuilds the view when ever one of the three tables is modified. Just a possibility.

Paul White SSC Guru Points: 150467 More actions · Answer 2

Lynn Pettis (1/12/2010)
Or, since this is SQL Server 2005, a DDL trigger on the three tables that dynamically rebuilds the view when ever one of the three tables is modified. Just a possibility.

That could probably be made to work (though I can't help pointing out the DDL triggers are scoped to the database and not to objects like tables). Won't be fun though!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Paul White (1/12/2010)
Lynn Pettis (1/12/2010)
Or, since this is SQL Server 2005, a DDL trigger on the three tables that dynamically rebuilds the view when ever one of the three tables is modified. Just a possibility.
That could probably be made to work (though I can't help pointing out the DDL triggers are scoped to the database and not to objects like tables). Won't be fun though!

Yes, it is scoped at the database level, but you can create the DDL trigger on the ALTER TABLE, and that trigger would need to check to see if it is one of the three tables involved in the view and recreate if it is.

sqlservercentral 9004 Grasshopper Points: 17 More actions · Answer 4

If I might just throw some gasoline on this fire... And please forgive me for adding onto this thread instead of creating a new one. But I'm after the same thing, and I think I can give you a good example why/what I want to do.

I'm looking for a way to create a database-level control on the "SELECT"ing of a particular table.

I need to prevent the results from including one column during a "SELECT *".

Specifically, I want to grant "SELECT..FROM tbl_Logins" to my developers, but I do NOT want them to get the "password" column in the results. Everything else is OK for them to have, just not the password column.

Is there some kind of "INSTEAD OF" trigger that might work for SELECT, that would allow "SELECT *" (Or even "SELECT name, password, last_login_date..."), but drop the password column from the resultset? Or, alternately, return alternate data, such as "*****" (literal string)?

Thanks for any suggestions.....

BTW - using SQL '08.