How to find which tables and views a user has access to?

We have a SQL Server 2008 database and have restricted all tables and views away from a specific user ID. Over the years we have granted back tables and views one at a time based on user need.

We had to do it this way because the vendor provides read/write access to the public role out of the box, so we had to create a role for this user, remove all access and grant back only what they needed.

Today we are going to create a replicated database containing only these tables and views so the user can run reporting on without degrading the production system. The issue is I'm not sure what tables and views this user has access to, because many people have granted access one at a time over the years.

Is there a query to check this users access?

PS Im an apps developer so please feel free to explain this as elementary as needed.

EDIT - How the role and access was originally created

```
------------------------------
-- CREATE ROLE db_finrep_deny
------------------------------

DECLARE @RoleName sysname
set @RoleName = N'db_finrep_deny'
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = @RoleName AND type = 'R')
Begin
DECLARE @RoleMemberName sysname
DECLARE Member_Cursor CURSOR FOR
select [name]
from sys.database_principals
where principal_id in (
select member_principal_id
from sys.database_role_members
where role_principal_id in (
select principal_id
FROM sys.database_principals where [name] = @RoleName AND type = 'R' ))

OPEN Member_Cursor;

FETCH NEXT FROM Member_Cursor
into @RoleMemberName

WHILE @@FETCH_STATUS = 0
BEGIN

exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName

FETCH NEXT FROM Member_Cursor
into @RoleMemberName
END;

CLOSE Member_Cursor;
DEALLOCATE Member_Cursor;
End
GO
-- dropping the role itself
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'db_finrep_deny' AND ty

You can use the function fn_my_permissions and impersonate the user to see which objects they have access to. You may want to expand the types to procedures and the various types of functions...

USE Your_Database;
 GO
 EXECUTE AS USER = N'the_user_name';
 GO
 SELECT 
    s.name,
    o.name,
    p.[permission_name]
 FROM sys.objects AS o 
 INNER JOIN sys.schemas AS s
 ON o.[schema_id] = s.[schema_id]
 CROSS APPLY sys.fn_my_permissions(QUOTENAME(s.name) 
   + N'.' + QUOTENAME(o.name), N'OBJECT') AS p
   WHERE o.[type] IN (N'U', N'V') -- tables and views
   AND p.subentity_name = N''; -- ignore column permissions 
 GO
 REVERT;

Only tested on compat level 100+; you may need to structure that slightly differently in older compatibility modes (see the first example here).

Since your actual role has been granted a bunch of implicit access to tables through authorization, perhaps try:

-- all the tables and views in the system
SELECT 
  schemaName = s.name,  
  objectName = o.name, 
  o.[object_id] 
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
WHERE type IN (N'U',N'V')
AND NOT EXISTS
(
  -- except those that have been *explicitly* denied
  SELECT 1
  FROM sys.database_permissions AS p
  WHERE p.class = 1
  AND p.state_desc = N'DENY'
  AND p.major_id = o.[object_id]
  AND p.grantee_principal_id IN (USER_ID(N'db_finrep_deny'),USER_ID(N'finrep'))
);

StackExchange Database Administrators Q#41234, answer score: 7