locked
advanced security RRS feed

  • Question

  • Hello – we have a schema called security_master and a user has full access to this schema. what is the syntax to give this user delete/insert/select access to a certain table and yet prevent the user from making any structural changes to that table? this user needs the ability to create new tables in the schema. thank you folks for the help.
    • Moved by Olaf HelperMVP Friday, February 27, 2015 9:03 AM Moved from "Database Engine" to a more specific forum.
    Thursday, February 26, 2015 10:38 PM

Answers

  • Hi

    You can use the following commands to achieve delete/insert/select access to a certain table and yet prevent the user from making any structural changes to that table

    GRANT DELETE,INSERT,SELECT on [TABLE_NAME] to [User_NAME] DENY ALTER ON [TABLE_NAME] to [User_NAME]

    For the second bit of your question USER needs CREATE table permission on database and ALTER SCHEMA permission on the SHEMA in which he needs to create the table

    USE [databasename]
    GRANT ALTER ON Schema :: [schemaname] TO [username]
    GRANT CREATE TABLE TO [username]
    GO
    

    Hope this helps

    Thanks

    Bhanu


    Thursday, February 26, 2015 10:48 PM