How do I see all the tables again?

I've been lying pretty low lately, doing research on some topics in a slightly different space (but at least obliquely SQL Server 2005 related). Came across a list of interesting student comments today, and I was back trying out (picking at?) one of my favorite spare-time topics to answer a question about. SQL Server 2005 metadata. The question was whether or not the INFORMATION_SCHEMA views were security-sensitive, and…of course they are. There are now (at least) 4 different ways to list the user tables in a database (all subject to security) and they are:

select * from sysobjects where type = 'U'
select * from sys.objects where type = 'U'
select * from sys.tables — I always wanted a systables in earlier versions
select * from INFORMATION_SCHEMA.tables where TABLE_TYPE = 'BASE_TABLE'

The Dec CTP closed the last loophole; using system procedures like sp_help is now permission sensitive.

But what if you WANT a user to be able to list all of the tables in the database? The “smallest“ permission you can give a user is VIEW DEFINITION privilege on a specific object. As an example, if I had a user named 'fred' that I wanted to 'see' the authors table, I would give:

grant view definition on object::authors to 'fred'

A specific resource is called a securable. Securables exist in a hierarchy. So although there's no straightforward way to give VIEW DEFINITION on all of the tables (that I can see) in a specific schema or database in a single statement, I could give a user access to all of the objects (Table, View, Function, Procedure, Queue, Type, Rule, Default, Synonym, Aggregate, XML Schema Collection) in a particular schema with this one statement.

grant view definition on schema::dbo to 'fred' — all in dbo schema
grant view definition on database::dbo to 'fred' — all in the database

Note the VIEW DEFINITION is the “smallest“ permission; it gives a user permission to see that an object exists without being able to see the data. For fred's case, “select * from authors“ would still fail. Giving SELECT also gives view definition privilege, so if you can SELECT against an object, it also shows up in your metadata list. Makes perfect sense. You can tell I'm liking the new metadata views…and the new “all permissions grantable“ concept. Wait until the first user calls up and asks “where did all of the tables in my dropdown list go?“.

2 thoughts on “How do I see all the tables again?

  1. Hi,

    Consider: we have sys.tables, sys.views, sys.procedures.

    So where is sys.functions?

    The original design idea behind the catalog views was mimimalist – only add a new view if there are extra attributes that required it. It turns out there are no extra attributes in a function that are not already present in sys.objects, therefore … no sys.functions.

    Unfortunately, I think that is counter-intuitive. We will consider adding

    sys.functions
    sys.system_functions
    sys.all_functions

    in the next release.

    For now, one easy way to get all functions without having to remember all the six different char(2) type codes is this

    select * from sys.objects where type_desc like ‘%FUNCTION’ — includes aggregates

    Regards,
    Clifford Dibble
    MSFT

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.