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?
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
(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]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With