SQL Server - Permission on a per table basis?

Question

I have a .net where I only have read access to the SQL Server database. Is it possible for SQL Server to give me write access to just ONE of the tables in the database, and restrict me to read only for the rest of the database?

Solution 1

Use this TSQL script, if you need:

EXEC sp_addrolemember N'db_datareader', N'User1';

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

Solution 2

In SQL Server Management Studio, right-click the read-only user in database name|security|Users and select Properties.

Select "Securables" and click "Search...". In the popup select "All objects of the type..." and click OK. Select "Tables" in the next window and click OK.

Then back in the Securables window, for each table that the user may write to: Click on the table, and in the Permissions window underneath, in the "Grant" column, select "Insert", "Select" and "Update".

Solution 3

Yes, yes it is.

Just grant yourself the ReadOnly role and give yourself explicit write permissions to the table in question.