none
Access to all but one table RRS feed

  • Question

  • Hi is there a way where i can give access to a user to the whole database (READ ONLY) except for couple of tables?

    Thanks

    Wednesday, September 18, 2019 2:58 PM

All replies

  • Hi,

    When you create an user, it doesn't have any permission. So you must grant access in server level or database level or table level or column level. The only way i see in your case is grant select permssion in all tables and revoke the select permission from those you want deny "read" access. You can use a piece of script for doing that!

    Regards,
    Rafael

    Wednesday, September 18, 2019 4:46 PM
  • Hi,

    Yes, it is possible creating a database role granting specific permissions to the user, and revoke select on the desired tables which yo do not want to grant access.

    CREATE ROLE [RoleName]
    GO
    GRANT SELECT ON [TABLENAME] TO [RoleName]
    GO
    sp_addrolemember [TEST], [UserName]

    -- deny access on tables
    REVOKE SELECT ON [TABLENAME] TO [RoleName]

    Regards.


    • Proposed as answer by Olaf HelperMVP Thursday, September 19, 2019 1:14 PM
    Wednesday, September 18, 2019 11:53 PM
  • Thanks will use a script.
    Thursday, September 19, 2019 1:02 PM