11

SQL Server 2008 :

How do I find out what kind of roles have what kind of permissions on a given table.

Thank you in advance.

shA.t
  • 16,580
  • 5
  • 54
  • 111
dotnet-practitioner
  • 13,968
  • 36
  • 127
  • 200

1 Answers1

9

You can get all permissions granted in the database and filter out for your table:

select permission_name, state, pr.name
from sys.database_permissions pe
join sys.database_principals pr on pe.grantee_principal_id = pr.principal_id
where pe.class = 1 
    and pe.major_id = object_id('<table_name>')
    and pe.minor_id = 0;

In addition you need to add the built in role permissions (db_owner, db_datareader, db_datawriter etc). Objects may also be accessed through ownership chaining.

You can always find out your own effective permission on any object by using fn_my_permissions('table_name', 'OBJECT')

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • the query works fine. A 'blank row' (an empty result set) is a valid response. Is actually the expected result for a table on which no explicit grants were issued. – Remus Rusanu Jun 16 '10 at 05:01
  • 1
    So... if I get a blank row... how do I know ... who has what permissions on this table? Thanks.. – dotnet-practitioner Jun 16 '10 at 16:33
  • 1
    There are implicit permissions, the ones derived from the fixed roles: db_owner members have all permissions, db_datareader members can select from it, db_datawriter member can update it etc etc. And all other permissions that are derived from ownership chaining. Read the link in the post. – Remus Rusanu Jun 16 '10 at 16:50
  • Try granting an explicit right, like `GRANT SELECT ON TO guest` and then run again the query to see the non-implicit permission grant.
     – Remus Rusanu Jun 16 '10 at 17:02