How do I create a user in SQL-Server that only has access to one table, and can only insert rows

  • I have an SQL server database, with soon to be two databases, which I use for a website.

    I already have a user account which is read-only for database 1 to search our products inventory. I'd like to create a seperate account for database 2 only, for table 1 ONLY, that ONLY allows inserting records (no update or delete or anything else). Im trying to be as redundant as possible with access restrictions (on top of code to try and prevent sql injection, if someone were to somehow get something through, I dont want the database itself to allow it).

    So bottom line question, How do I create a user in SQL server that has restricted access to only x table in y database and can only read/insert records, and nothing else?


    create the user, don't give any roles like db_datareader or db_datawriter

    then GRANT INSERT ON YourTable TO SomeUser

    if you want insert and select


    (1) To give a user with limited access to one Table only

    GRANT SELECT ON [schemaName].[tableName] to [username]

    (2) To grant INSERT Permission.

    GRANT INSERT ON [schemaName].[tableName] TO [username]

    Sunil Kumar

    Syntax for granting access to a single table:

    GRANT access_type ON table_name TO [user name]

    Syntax for revoking access for a single table:

    REVOKE access_type ON table_name FROM [user name]

    where access_type can be SELECT, INSERT, DELETE...