Using Views to Simplify Retrieval of Data

When querying data, it is often required that complex joins, aggregates, functions, and so on, be written in to the SELECT statement to produce the desired results. To mask this complexity, the SELECT statement can be created as a view, and then future queries can be performed against the view. The information_schema views provided by Microsoft are a prime example of this use of a view. Because the system tables used to store metadata are quite complex, developing queries to retrieve information about SQL Server can be a daunting task. By querying the information_schema views instead of the system tables, you can simplify your SELECT statements. Following is a simple SELECT statement to query the information_schema.table_privileges view:

SELECT * from Information_schema.table_privileges 

This query will obtain information about permissions in the database.

In Listing 27.1, you see the actual SELECT statement with which the table_privileges view is defined. Both statements return the same data, so it's up to you to decide which you would rather type!

Listing 27.1 SELECT Statement Defining the information_schema.table_privileges View

select
        user_name(p.grantor)  as GRANTOR
       ,user_name(p.uid)             as GRANTEE
       ,db_name()                            as TABLE_CATALOG
       ,user_name(o.uid)             as TABLE_SCHEMA
       ,o.name                                     as TABLE_NAME
       ,case p.action
               when 26  then 'REFERENCES'
               when 193 then 'SELECT'
               when 195 then 'INSERT'
               when 196 then 'DELETE'
               when 197 then 'UPDATE'
        end                                         as PRIVILEGE_TYPE
       ,case
               when p.protecttype = 205 then 'NO'
               else 'YES'
        end                                        as IS_GRANTABLE
 from
        sysprotects p,
        sysobjects o
where
        (is_member(user_name(p.uid)) = 1
        or
               p.grantor = user_id())
   and (p.protecttype = 204 or  /*grant exists without same grant with grant */
   (p.protecttype = 205
               and not exists(select * from sysprotects p2
                              where p2.id = p.id and
                              p2.uid = p.uid and
                              p2.action = p.action and
                              p2.columns = p.columns and
                              p2.grantor = p.grantor and
                              p2.protecttype = 204)))
       and p.action in (26,193,195,196,197)
       and p.id = o.id
       and o.xtype in ('U', 'V')
       and 0 != (permissions(o.id) &
               case p.action
          when 26  then  4               /*REFERENCES basebit on all columns */
          when 193 then  1               /*SELECT basebit on all columns     */
          when 195 then  8               /*INSERT basebit */
          when 196 then  16              /*DELETE basebit */
          when 197 then  2               /*UPDATE basebit on all columns     */
               end)