Post reply

Granting datareader access to a user for objects in a schema

  • July 3, 2012 at 9:44 am

    #392648

    Hi

    When trying to grant datareader access to a user in a non dbo schema he is unable to view the tables in it.

    I had thought that if I give him datareader and run:

    GRANT VIEW DEFINITION ON SCHEMA :: xxx TO [xxxx\xxx]

    Then he would be able to view the objects in that schema, however this is not working.

    What am I doing wrong?

    Thanks

  • July 3, 2012 at 10:06 am

    #1508306

    Does the user have any permissions denied? That will trump what you have granted.

    Jared
    CE - Microsoft

  • July 3, 2012 at 12:46 pm

    #1508429

    No was a new login

  • July 3, 2012 at 12:51 pm

    #1508431

    Run this:

    SELECT USER_NAME(dpe.grantee_principal_id) AS [UserName],

    dpr.type_desc AS principal_type_desc,

    dpe.class_desc,

    OBJECT_NAME(dpe.major_id) AS object_name,

    dpe.permission_name,

    dpe.state_desc AS permission_state_desc

    FROM sys.database_permissions dpe

    INNER JOIN sys.database_principals dpr

    ON dpe.grantee_principal_id = dpr.principal_id

    WHERE

    USER_NAME(dpe.grantee_principal_id) = 'domain\user' and see if there are any DENYs in the last column.

    Jared
    CE - Microsoft

  • July 3, 2012 at 12:51 pm

    #1508432

    Is the user not able to list the tables out in SSMS? Not able to edit the tables? Not able to script the tables?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • July 3, 2012 at 1:37 pm

    #1508460

    Correct he was saying he couldn't see the list of tables in SSMS. I will run the script tomorrow when I get back to work 🙂

  • July 3, 2012 at 1:41 pm

    #1508464

    Kwisatz78 (7/3/2012)

    Correct he was saying he couldn't see the list of tables in SSMS. I will run the script tomorrow when I get back to work 🙂

    it's really easy to test that using the EXECUTE AS :

    --change to your user name

    EXECUTE AS USER='ClarkKent'

    SELECT USER_NAME() --I'm Clark Kent!

    SELECT * FROM sys.tables; --do i see any tables in the schema?

    SELECT * FROM sys.objects; --do i see any objects in the schema?

    SELECT * FROM SomeTableThatExists --was this user given db_datareader or similar permissions?

    --change back into superman:

    REVERT;

    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!

  • July 4, 2012 at 4:09 am

    #1508676

    Hi - so the user does have permissions to query tables and sys.objects, however the list of tables does not appear in SSMS. How can I get this list to appear?

  • July 5, 2012 at 9:06 am

    #1509167

    I would log in as that user from a different location such as your machine. I suspect the user may have a filter being applied in SSMS.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 9 posts - 1 through 9 (of 9 total)

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