Verify Truncate / Alter Permission to Log In

Question

How to check if login has truncate permission for a specific table?

We have a login with the name Test , for which we have granted ALTER permission only for individual tables. Now I want to get a list of tables for which Test has ALTER permission.

Checked in google and forum could not find the answer.

Answer 1

Assuming you have the ability to impersonate a 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 want to list permissions for a specific user, try the query below.

 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 can also use the following function to quickly check the permissions available to a specific user.

MS link Documentation that provides you with useful usage information is provided in the link.

Syntax:

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