List all tables that a specific login has permissions

Question

This might be repetitive, but I haven't found a solution that serves my purpose 100%. What I'd like to achieve is to have a script (or SP) that'll list all tables that a user has permissions on. Any insight will be helpful. PS: I'm not very good with TSQL. :(

Thanks a lot.

A login is mapped to a user in a database and that user is granted permissions. Also, you can use the query found here — Kin Shah, Aug 07 '14 at 22:37

score 1 · Answer 1 · answered Aug 08 '14 at 08:56

the script I use will show you all objects (SP, Tables, Functions) for a specified Database name and all the users that have rights on them, but you can narrow the search and extract exactly what you need.

DECLARE @EXEC_SCRIPT    NVARCHAR(4000)
DECLARE @DBNAME         NVARCHAR(256) = 'DB name'

SET @EXEC_SCRIPT = 'USE ['+ @DBNAME + '] 
select DB_NAME ()                   as [db_name],
a.class_desc                        as [class_type] ,  
user_name(a.grantee_principal_id)           as [user_name] ,
isnull(OBJECT_NAME(a.major_id),'''')            as [object_name],
isnull      (b.name,'''')               as [column_name] , 
a.permission_name                   as [permission_name] , 
a.state_desc                        as [permission_type] , 
c.type_desc                     as [object_type],
CASE d.type 
                WHEN ''A'' THEN N''ApplicationRole''
                WHEN ''R'' THEN N''Role''
                ELSE N''User'' END  as [security_type],
CASE WHEN a.major_id  < 0 THEN N''Y''
     ELSE N''N'' END                as [system_object],
d.is_fixed_role                     as [is_fixed_role]

from sys.database_permissions a left join sys.columns b 
on a.minor_id = b.column_id and a.major_id = b.object_id
left join sys.objects c on  c.object_id = a.major_id
INNER JOIN sys.database_principals AS d ON d.principal_id = a.grantee_principal_id
where a.class_desc <> ''DATABASE''  and user_name(a.grantee_principal_id) <> ''public''
order by user_name'

EXEC master.dbo.sp_executesql @EXEC_SCRIPT

score 0 · Answer 2 · answered Aug 08 '14 at 01:13

Off the top of my head (no MSSQL boxes up at the moment, sorry):

EXECUTE AS <database user> ;
SELECT * FROM sys.objects WHERE type = 'u' ;

Metadata visibility means that if a database user queries sys.objects then they will only see the objects they have permissions to access or that they own.

If you require the actual permissions they have on those objects then you need to query the permissions management views, as noted in Kin's comment above.

score 0 · Answer 3 · answered Aug 08 '14 at 05:48

I wrote a script a while ago, that does just this. I've posted it on my blog, http://sqlsunday.com/downloads/.

Remember that a Windows user can be a member of a Windows group, and in SQL Server, you can't see those memberships, so you'll have to look at Windows users and Windows groups separately, if you're doing a security audit, for instance.

If you want to read up on SQL Server security, there's also a series of posts on the blog on that subject.

List all tables that a specific login has permissions

3 Answers3