CODEWITHSUNDEEP
sqlsql-server
learningtech
learningtech

Reputation: 33725

SQL Server - Permission on a per table basis?

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?

Upvotes: 20

Views: 31675

Answers (3)

Daren
Daren

Reputation: 179

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".

Upvotes: 8

p.campbell
p.campbell

Reputation: 100637

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

Upvotes: 29

RQDQ
RQDQ

Reputation: 15579

Yes, yes it is.

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

Upvotes: 4

Related Questions