Checking Truncate/Alter Permission for a login

Question

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 1

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;

Answer 2

If you wanted to list out the permissions against a particular user ,Try out with the below query.

SELECT  OBJECT_NAME(major_id) TableName,PERMISSION_NAME, STATE_DESC,  U.name UserName 
FROM sys.database_permissions P 
    JOIN sys.tables T ON P.major_id = T.object_id 
    JOIN sysusers U ON U.uid = P.grantee_principal_id
WHERE U.name='Test'

Answer 3

You could use the following function as well to quickly check the permissions that are available for a specific user.

MS SQL Documentation that will give you some useful information on its usage is provided in the link.

Syntax:

SELECT * FROM sys.fn_my_permissions('TEST', 'USER');