Paul White NZ (3/30/2010)
Hugo Kornelis (3/30/2010)
As far as I know, that was once the case. In SQL Server 7.0 or so.It's still in Itzik's Inside SQL Server 2008 T-SQL Querying book 🙂
Do you want to test it or should I? 😉
The effect was always, as I said, vanishingly small. I always use the star syntax. As I think I mentioned.
I tested it. Below is the code I ran. It shows that both SELECT * and SELECT 1 test for both table- and column-level permissions. (Try changing the granted and denied privileges - I was not able to find any way to get the SELECT * to behave other than the SELECT 1).
create table x (a int, b int);
go
create user TestUser without login;
go
deny select on x to TestUser;
grant select on x(b) to TestUser;
go
if exists (select * from x) print 'Aye';
if exists (select 1 from x) print 'Aye';
if exists (select a from x) print 'Aye';
go
execute as user='TestUser';
go
if exists (select * from x) print 'Aye';
if exists (select 1 from x) print 'Aye';
if exists (select a from x) print 'Aye';
go
revert;
go
drop user TestUser;
drop table x;
go