Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
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.? –  Aaron Bertrand Apr 30 '13 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). –  ProfessionalAmateur Apr 30 '13 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 Apr 30 '13 at 15:34

1 Answer 1

up vote 4 down vote accepted

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 
    SCHEMA_NAME(o.[schema_id]),
    o.name,
    p.[permission_name]
 FROM sys.objects AS o 
 CROSS APPLY sys.fn_my_permissions(o.name, N'OBJECT') AS p
   WHERE o.[type] IN (N'U', N'V') -- tables and views
   AND subentity_name = N''; -- ignore column permissions 
 GO
 REVERT;

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

SELECT name, [object_id]
FROM
(
    -- all the tables and views in the system
    SELECT [object_id], name FROM sys.tables
    UNION ALL 
    SELECT [object_id], name FROM sys.views
) AS t
WHERE NOT EXISTS
(
  -- except those that have been *explicitly* denied
  SELECT 1
  FROM sys.database_permissions 
  WHERE class = 1
  AND state_desc = N'DENY'
  AND major_id = t.[object_id]
  AND grantee_principal_id IN (USER_ID('db_finrep_deny'),USER_ID('finrep'))
);
share|improve this answer
    
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? –  ProfessionalAmateur Apr 30 '13 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? –  Aaron Bertrand Apr 30 '13 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. –  ProfessionalAmateur Apr 30 '13 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. –  Aaron Bertrand Apr 30 '13 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. –  Aaron Bertrand Apr 30 '13 at 15:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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