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

Question

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 type = 'R')
DROP ROLE [db_finrep_deny]
GO


------------------------------
-- Grant access (read/write/select/upd) to only select tables
------------------------------
CREATE ROLE [db_finrep_deny]
AUTHORIZATION [dbo]
GO
exec sp_addrolemember 'db_finrep_deny', 'finrep'
GO

-- run the dynamic sql generated by this statement
select  'DENY SELECT, INSERT, UPDATE, DELETE ON '
        + ss.name
        + '.'
        + st.name
        + ' TO db_finrep_deny;'
from sys.tables st
inner join sys.schemas ss on st.schema_id=ss.schema_id
where st.name not in ('TABLE1','TABLE2','TABLE3','TABLE4')
order by ss.name, st.name;

grant select on dbo.vwFINMASTER to db_finrep_deny;

---

Aaron - this is what I get when I run your query, it shows only views, but no tables

---

(No column name)    name    permission_name
dbo     dtproperties    REFERENCES
dbo     vwFINMASTER     SELECT
dbo     vwLEDGERVIEW    SELECT

Answer 1

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