List permission tables

This SQL sentences allows to knows the permissions of one table or all tables that are currently in you database.

The fist one shows the permissions of one table:

select object_name(major_id) as object,
user_name(grantee_principal_id) as grantee,
user_name(grantor_principal_id) as grantor,
permission_name,
state_desc
from sys.database_permissions
where major_id = object_id('XXX_TABLE')
and class = 1

This other shows the permissions of all tables:

select * From fn_my_permissions(NULL, 'XXX_DATABASE')
Etiquetas: , ,