Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 841
Kizzelwhix Avatar asked Mar 04 '13 14:03

Kizzelwhix


Video Answer


2 Answers

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

GRANT INSERT, SELECT ON YourTable TO SomeUser

like image 51
SQLMenace Avatar answered Sep 28 '22 17:09

SQLMenace


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

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

(2) To grant INSERT Permission.

GRANT INSERT ON [schemaName].[tableName] TO [username]
like image 30
Muhammad Hani Avatar answered Sep 28 '22 15:09

Muhammad Hani