THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Using the catalog views to see table permissions

I was needing to get an idea of the permissions that had been granted in a couple of databases the other day, and being the GUI novice I am, couldn't find what I wanted easy enough (if it even is in there.)  So, I hunted around in the system views to see what I could find.  There are two views of major interest for permissions: sys.database_permissions and sys.server_permissions.  So I formulated the following query:

select   database_permissions.permission_name,
           coalesce(objects.type_desc,database_permissions.class_desc)
                           + case when objects.type_desc is not null and minor_id > 0 then '-COLUMN'
                                      else '' end as object_type,
           case database_permissions.class_desc
                  when 'SCHEMA' then schema_name(major_id)
                  when 'OBJECT_OR_COLUMN' then
                                  case when minor_id = 0 then object_name(major_id)
                                   else (select object_name(object_id) + '.'+ name
                                          from sys.columns 
                                          where object_id = database_permissions.major_id 
                                             and column_id = database_permissions.minor_id) end
                  else 'other' end as object_name,
           database_principals.name as database_principal,
           database_permissions.state_desc as grant_state
from    sys.database_permissions
               join sys.database_principals
                     on database_permissions.grantee_principal_id = database_principals.principal_id
               left join sys.objects --left because it is possible that it is a schema
                     on objects.object_id = database_permissions.major_id
where database_permissions.major_id > 0
    and permission_name in ('SELECT','INSERT','UPDATE','DELETE')
order by object_name

The columns returned are:

  • permission_name - SELECT, INSERT, UPDATE, DELETE (only dealing with table rights in this query)
  • object_type - The type of objects, view, table, function.  Based on values from sys.database_permissions
  • object_name - the table, view or function, plus the column if it is a column view
  • database_principal - the user who has the rights granted or denied
  • grant_state - if the right has been given (GRANT) or explicitly disallowed (DENY)

As a demonstration, consider the following batch (I ran this in my tempdb):

create user bob without login
create user fred without login
create user silvio without login
go
create table test
(
   testId int,
   value varchar(10)
)
go
grant select on test to fred
grant insert, update, delete on test to fred
go
grant select on test to bob
deny select on test(value) to bob
go

grant select on schema::dbo to silvio
go


create function test$select()
returns table
as
return (select *
           from test)
go
grant select on test$select to public
go

create view testNoNullValue
as
   select *
   from test
   where value is not null
go
grant select on testNoNullValue to fred,bob
go

Run the query and you should see these results, showing the schema, the tables, the view, the function, and who had rights granted:

permission_name object_type                      object_name        database_principal grant_state
--------------- -------------------------------- ------------------ ------------------ ------------
SELECT          SCHEMA                           dbo                silvio             GRANT
SELECT          USER_TABLE                       test               bob                GRANT
DELETE          USER_TABLE                       test               fred               GRANT
INSERT          USER_TABLE                       test               fred               GRANT
SELECT          USER_TABLE                       test               fred               GRANT
UPDATE          USER_TABLE                       test               fred               GRANT
SELECT          SQL_INLINE_TABLE_VALUED_FUNCTION test$select        public             GRANT
SELECT          USER_TABLE-COLUMN                test.value         bob                DENY
SELECT          VIEW                             testNoNullValue    bob                GRANT
SELECT          VIEW                             testNoNullValue    fred               GRANT

Coming up soon, a version of the query for execute rights.

Crossposted to: http://drsql.spaces.live.com

Published Saturday, November 11, 2006 10:51 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Mathew said:

Fantastic piece of code. I'm trying to update an 'audit' script by phasing out soon-to-be-deprecated commands (such as sp_helprotect) and use catalog views instead. Well done.

May 9, 2012 2:00 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement