Post reply

Looking for query to show who has data_reader in a database

  • January 14, 2012 at 1:45 pm

    #249616

    I found the use of Information_schema to see who has select to tables in a database but I need to see who has data_reader access in a SQL Server 2008 database and I cannot seem to figure out the query for that. Can someone help this?

  • January 14, 2012 at 5:40 pm

    #1433767

    Markus this will get you started; this does a first level who's in which role; it does not handle nested roles, where eventually someone in a role, which is assigned the role, gets the permission:

    select WhoAmI.name As UserOrRole,

    RolesIOwn.name as InThisRole

    from sys.database_role_members members

    left outer join sys.database_principals WhoAmI

    ON members.member_principal_id = WhoAmI.principal_id

    left outer join sys.database_principals RolesIOwn

    ON members.role_principal_id = RolesIOwn.principal_id

    --where RolesIOwn.name = 'db_datareader'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 15, 2012 at 9:08 am

    #1433837

    Thanks Lowell. WOrks like a charm!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply