locked
How to give read & write permissions to a specific table in a database of SQL 2008R2? RRS feed

  • Question

  • Hi All,

    How to give read & write permissions  to a specific table in a database of SQL 2008R2?

    Please suggest on this.

    Regards,

    Kalyan


    ----Learners Curiosity Never Ends----

    Monday, August 19, 2013 2:50 PM

Answers

  • Reading data from a table requires the SELECT permission. Writing to a table consists of INSERT permission to add new rows, or UPDATE permission to change an existing role. And the DELETE permission will let someone remove a row.

    You can add all or just some of these permissions using SQL Server Management Studio by selecting the GRANT box for each of those permissions. General instructions for doing that are at http://technet.microsoft.com/en-us/library/ff878066.aspx though the example used is a stored procedure instead of a table. (You right-click a table in Management Studio Object Explorer, select Properties, then pick the Permissions page. In Users or Roles add the user. Then in the bottom part click the boxes that you want.

    You can also grant these permissions using Transact-SQL Statements, such as:

    GRANT SELECT ON OBJECT::dbo.Table1 TO Kalyan;
    GRANT INSERT ON OBJECT::dbo.Table1 TO Kalyan;
    GRANT UPDATE ON OBJECT::dbo.Table1 TO Kalyan;
    GRANT DELETE ON OBJECT::dbo.Table1 TO Kalyan;


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, August 19, 2013 3:23 PM
    Answerer

All replies

  • Is there any possibility to allow db user for a specific table?

    Regards,

    Kalyan


    ----Learners Curiosity Never Ends----

    Monday, August 19, 2013 3:04 PM
  • Try the below:

    create a user and use the below:

    
    GRANT INSERT, UPDATE, SELECT ON 
       MyTable 
    TO User1 --for multiples, it's   TO User1,User2


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Monday, August 19, 2013 3:13 PM
    Answerer
  • Reading data from a table requires the SELECT permission. Writing to a table consists of INSERT permission to add new rows, or UPDATE permission to change an existing role. And the DELETE permission will let someone remove a row.

    You can add all or just some of these permissions using SQL Server Management Studio by selecting the GRANT box for each of those permissions. General instructions for doing that are at http://technet.microsoft.com/en-us/library/ff878066.aspx though the example used is a stored procedure instead of a table. (You right-click a table in Management Studio Object Explorer, select Properties, then pick the Permissions page. In Users or Roles add the user. Then in the bottom part click the boxes that you want.

    You can also grant these permissions using Transact-SQL Statements, such as:

    GRANT SELECT ON OBJECT::dbo.Table1 TO Kalyan;
    GRANT INSERT ON OBJECT::dbo.Table1 TO Kalyan;
    GRANT UPDATE ON OBJECT::dbo.Table1 TO Kalyan;
    GRANT DELETE ON OBJECT::dbo.Table1 TO Kalyan;


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, August 19, 2013 3:23 PM
    Answerer