I'm used to working on contracts where someone else is responsible for the SQL database. However, I'm now working a contract where I must set up a new SQL Server, so I set up MS SQL Server 2012 Express.
I've written an application that connects to the SQL server and reads/updates several tables. In my test environment, the application is connecting using the sa account, which is, of course, a big no-no.
I want to create a user account that will be able to select/insert/update/delete from a limited set of tables and have no other privileges what-so-ever.
I would follow along with some of the instructions that I see on Google, but I keep seeing questions regarding unprivileged accounts unexpectedly having the right to do things that the account creator didn't intend, so I wanted to ask the experts:
How do I create a SQL account that can only do one thing: select/insert/update/delete from a limited set of tables?
Edit: Based on the feedback I've received, I did some research and wrote the following script:
CREATE LOGIN ExtraUser WITH PASSWORD = 'foobar'; USE MyDatabase; CREATE USER ExtraUser FOR LOGIN ExtraUser; GRANT SELECT ON dbo.abc; GRANT SELECT ON dbo.def; GRANT INSERT ON dbo.def;
This appears to do what I want, but I would like to know if I can trust that this user can't access anything other than the two tables to which I've given him access.