Post reply

Tables that don''t appear in INFORMATION_SCHEMA.Columns

  • 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.

  • July 6, 2005 at 6:20 am

    #571184

    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

  • July 6, 2005 at 6:26 am

    #571186

    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.

  • July 6, 2005 at 6:29 am

    #571188

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

  • July 6, 2005 at 6:31 am

    #571189

    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

  • July 6, 2005 at 6:41 am

    #571195

    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.

  • July 6, 2005 at 7:15 am

    #571213

    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

  • July 7, 2005 at 1:39 am

    #571660

    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.

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

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