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)