Post reply

Deny access to a table

  • November 22, 2016 at 5:24 am

    #337626

    Hi All,

    Ahh permissions how I loof thee!

    Right so, we want to deny access so only sysadmins have access to a certain table (obviously we have access anyway, but want to make sure no one else has access).

    It's been a while, but I created a role. I created a new user and didn't add this role to them however they could still see the table.

    I restricted the table so that only that role would have access to it and this user only then had db_reader access generally. A schema was also set up (this was before I waded in) and if I put them as a DENY permission in there they couldn't see it.

    So best way to stop anyone other than us god like users having access to the table?

  • November 22, 2016 at 5:27 am

    #1913955

    Create a role that has DENY SELECT on that table, assign that role to the users. As long as they don't have sysadmin access, they will not be able to see that table (though if they have access to stored procedures that select from that table, they can still run those)

    DENY has precedence over GRANT.

    Also, a newly created user will have no rights to anything, unless your DB has a lot of permissions granted to the Public role (which is not a good practice)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 22, 2016 at 6:45 am

    #1913980

    Thank you, I think I got too embroiled in GRANT rather than DENY.

    I tested it (of course) and works like a charm. Thank you.

  • November 22, 2016 at 9:09 am

    #1914038

    Read info here https://msdn.microsoft.com/en-us/library/aa846430(v=ax.10).aspx

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

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