dotnet-practitioner dotnet-practitioner - 1 year ago 106
SQL Question

SQL Server: Permissions on table

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.

Answer Source

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

select permission_name, state,
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')

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download