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


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

        FETCH NEXT FROM Member_Cursor
        into @RoleMemberName

    CLOSE Member_Cursor;
    DEALLOCATE Member_Cursor;
-- 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]

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

-- run the dynamic sql generated by this statement
        + 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
  • And again, how do you know that tables are supposed to show up here? How do you know that developers granted explicit access over the years, whether they did so successfully, whether they used the user or the role, etc. etc.? Commented Apr 30, 2013 at 15:28
  • If Im understanding the question correctly, the users who connect with the FINREP user ID can access and query the tables 1-4 with no problem, but cannot access other tables. Does that help? I need to find what they have access to (whether it was granted in a manner similar to the views or unrestricted). Commented Apr 30, 2013 at 15:32
  • 1
    @ProfessionalAmateur Aaron's solution works as I have tested it on my local server. If you want more details of all the permissions then you can use stackoverflow.com/questions/7048839/…
    – Kin Shah
    Commented Apr 30, 2013 at 15:34

1 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;
 EXECUTE AS USER = N'the_user_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 

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
  schemaName = s.name,  
  objectName = o.name, 
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')
  -- except those that have been *explicitly* denied
  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'))
  • That worked for the Views but it didnt show any of the tables. This user has SELECT access only to a handful of tables, would that make a difference for this query? Commented Apr 30, 2013 at 15:10
  • Shouldn't, are you sure they have explicit SELECT access and not via group/role etc.? Where/how have you verified this? Commented Apr 30, 2013 at 15:13
  • I'll paste the command above (in original question post) that the DBA provided me when we originally created this role and access. It might help explain what we are doing, Im probably not doing a great job of explaining the setup. Commented Apr 30, 2013 at 15:15
  • So I see the script has dropped / re-created the role (with authorization dbo - WHY?), then denied access to everything, then granted select on a single view. Were you expecting my query to show tables that haven't been explicitly granted SELECT? Otherwise I'm not sure how the script you've added demonstrates that this role does have access to more than the one view. Commented Apr 30, 2013 at 15:22
  • Also, I tested this using a role approach similar to the one in the question, and I was able to show both tables and views that were explicitly granted access. Commented Apr 30, 2013 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.