Prdp Prdp - 2 months ago 9
SQL Question

Checking Truncate/Alter Permission for a login

How to check whether a login has

truncate
permission for a particular table?

We have a login called
Test
for which we have given
ALTER
permission to particular tables alone. Now I want get the list of tables for which
Test
login has
Alter
permission.

Checked in google and forum couldn't find any answer.

Answer

Assuming that you have the ability to impersonate the user, you can do the following:

execute as user = 'Test';

select p.*
from sys.tables as t
cross apply sys.fn_my_permissions(t.name, 'OBJECT') as p
    where permission_name = 'ALTER';

revert;
Comments