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.

Try this: http://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database — sagi, Sep 19 '16 at 10:21
@sagi - Thanks, the link did help me to find the answer but not exactly what i was looking for. Will post answer soon — Pரதீப், Sep 19 '16 at 10:30

Ben Thul · Accepted Answer · 2016-09-19T13:05:32.477

3

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;

edited Sep 19 '16 at 13:05

answered Sep 19 '16 at 12:51

Ben Thul

31,080
4
45
68

Could not find login details in above query – Pரதீப் Sep 19 '16 at 12:55
Did you read/understand the code? Or did you just copy and paste it into your Management Studio Window and run it? If the latter without the former, that's a really dangerous habit. You have to change the name of the user that you're impersonating in order to get the permissions for that user. I've updated the code accordingly – Ben Thul Sep 19 '16 at 13:07
Extremely sorry didn't read your answer properly.. One of my worst day at office.. Executed with proper user and it showed exactly what I am looking for – Pரதீப் Sep 19 '16 at 13:13

Unnikrishnan R · Answer 2 · 2016-09-19T11:58:03.927

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'

edited Sep 19 '16 at 11:58

answered Sep 19 '16 at 10:28

Unnikrishnan R

4,965
1
12
21

It is not returning any result – Pரதீப் Sep 19 '16 at 11:37
@prdp : i tried,its working..please see the screen shot. – Unnikrishnan R Sep 19 '16 at 11:57

score 1 · Answer 3 · answered Sep 19 '16 at 11:08

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');

Checking Truncate/Alter Permission for a login

3 Answers3