none
How To Grant User SELECT, UPDATE, DELETE, & INSERT

    Question

  • I am told to give a user SELECT, UPDATE, DELETE, & INSERT.  He is the owner of the data and he will need to alter it.  If I give this user db_datareader and db_datawriter, does this fullfill this request?  Or do I need to create a role and grant with the following command

    GRANT SELECT, INSERT, UPDATE, DELETE TO RoleNameHere

    to give him the permissions that he needs to alter the data?

     

     


    lcerni

    Monday, November 18, 2013 3:28 PM

Answers

  • Per your description above, assigning roles db_datareader and db_datawrtier should work for you. 

    The only difference is when you assign the fixed roles, you give them permissions across all the tables in that schema where as by Granting exculsivley, you can limit to specific tables.. so, if you need to give permissions on all tables in the schema, just use the fixed roles else go  with GRANT SELECT, INSERT, UPDATE, DELETE  on TableName TO User/RoleName 


    Hope it Helps!!


    Monday, November 18, 2013 4:12 PM
  • Hello,

    Yes db_datareader and db_datawriter is enough if he/she is going to modify the data alone.

    If you also want give him the privilege of altering the table structure, then you need to provide hime alter table premission also.

    Thanks

    Monday, November 18, 2013 4:18 PM

All replies

  • Per your description above, assigning roles db_datareader and db_datawrtier should work for you. 

    The only difference is when you assign the fixed roles, you give them permissions across all the tables in that schema where as by Granting exculsivley, you can limit to specific tables.. so, if you need to give permissions on all tables in the schema, just use the fixed roles else go  with GRANT SELECT, INSERT, UPDATE, DELETE  on TableName TO User/RoleName 


    Hope it Helps!!


    Monday, November 18, 2013 4:12 PM
  • Hello,

    Yes db_datareader and db_datawriter is enough if he/she is going to modify the data alone.

    If you also want give him the privilege of altering the table structure, then you need to provide hime alter table premission also.

    Thanks

    Monday, November 18, 2013 4:18 PM