Post reply

select permission denied on object

  • May 21, 2012 at 12:56 pm

    #256836

    I have created a database role that denies all but select on an object. I assigned this role to a user and the user is getting select permission denied when it is explicitely granted in the database role. I removed the user from the role and they are able to select but when added to the role the select permission is denied. Again SELECT is the only GRANTED permission to this table in the role. Any idea what could be happening here?

  • May 21, 2012 at 12:58 pm

    #1490634

    Is there something that DENYs select on that object. DENY will always trump GRANT.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • May 21, 2012 at 1:02 pm

    #1490636

    No deny for select just everything else

  • May 21, 2012 at 2:45 pm

    #1490729

    still sound slike multiple roles are hitting the user;

    i would test as that user and see what roles that login was in;

    --see what windows roles the windows user belongs to:

    EXEC master..xp_logininfo @acctname = 'mydomain\lowell',@option = 'all' -- Show all paths a user gets his auth from

    EXEC master..xp_logininfo @acctname = 'mydomain\authenticatedusers',@option = 'members' -- show group members of a group from the domain

    --put on the user's hat, so to speak.

    EXECUTE AS USER ='mydomain\lowell'

    --is my disguise working?

    select suser_name();

    --look at my that permissions:

    select * from fn_my_permissions(null,'SERVER')

    select * from fn_my_permissions(null,'DATABASE')

    --is there a DENY in there?

    select * from fn_my_permissions(null,'DATABASE')

    WHERE permission_name LIKE 'DENY%'

    --change back into myself:

    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!

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

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