Tables that don''t appear in INFORMATION_SCHEMA.Columns

Question

Tables that don''t appear in INFORMATION_SCHEMA.Columns

Bill Geake

Ten Centuries

Points: 1352
More actions
July 6, 2005 at 6:14 am

#88916

A colleague is running trhis script on one of "my" databases:
select * from INFORMATION_SCHEMA.Columns
There are no records for some tables which do have columns (and are in use). Why should this be? Should I be worried? Should I just point my colleague at sysobjects etc?
Bill.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

A common factor for all of the INFORMATION_SCHEMA views is that they will show only what the current user has permissions on.

So, what permissions does your colleague's login have on those missing tables?

/Kenneth

Bill Geake Ten Centuries Points: 1352 More actions · Answer 2

Full permissions - even as far as being an admin on the box itself. I'd be very surprised if this were the cause though I am no expert on SQL Server permissions.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Check the missing tables permissions. Maybe there's an hidden deny on the table.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

Well, depending on how he logs on (windows auth or SQL login) it may not do any difference if he is admin on the win box or not.

Unless he's running the query as sa or sysadmin, do check that his login maps to a user in said db that actually has permissions on those missing tables. (or columns, since permissions can be granted/denied at column level as well)

In any case, permission issues is the only thing I can think of right now that would show up this way. You might do a select straight from syscolumns to verify that the stuff you're looking for really is there

/Kenneth

Bill Geake Ten Centuries Points: 1352 More actions · Answer 5

The tables and columns really are there - they have data in them and they appear in syscolumns and sysobjects.

When I run the INFORMATION_SCHEMA query myself, I don't see these tables either. I created the tables and use them daily - if I can select, update, delete, insert and drop these things then I guess I have pretty good permissions on them.

Odd.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

If you pick out a table and a missing columnname and run this query, do you get something else besides a zero as return value?

select permissions(object_id('dbo.myTable'), 'myColumn')

/Kenneth

Bill Geake Ten Centuries Points: 1352 More actions · Answer 7

From the colleague's perspective, I do get zero. So it must be a permissions thing. I think we have a tidy-up to do; our Operations team is supposed to have granted everyone in my team select permissions across the board for support purposes.

Thanks for sharing your expertise!

Bill.