How to create SQL account that can only perform certain actions?

Question

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.

To start, you will need to create an account that has datareader and datawriter roles. — DanBig, Oct 09 '13 at 18:47
Related: http://stackoverflow.com/questions/1134319/difference-between-a-user-and-a-login-in-sql-server — Vivian River, Oct 09 '13 at 19:17

squillman · Accepted Answer · 2013-10-09T19:51:29.743

You're almost there. Adding the user to db_datareader and db_datawriter indeed grants global read/write access to the database.

You will need to use GRANT to grant access to the login for specific objects.

GRANT SELECT ON dbo.Table1 TO ExtraUser
GRANT INSERT ON dbo.Table1 TO ExtraUser
GRANT UPDATE ON dbo.Table1 TO ExtraUser
GRANT DELETE ON dbo.Table1 TO ExtraUser

You can also combine the permissions as necessary:

GRANT SELECT,INSERT,UPDATE,DELETE on dbo.Table1 to ExtraUser

Note: you will need to run that for each table you wish to allow access to

More information on GRANT can be found here.

EDIT:
In response to your edit, yes. It will restrict that user to those permissions, so long as you have not also granted something more overpowering (like adding the login to db_datareader and db_datawriter). If you have, then you should revoke those higher level permissions using REVOKE.

How to create SQL account that can only perform certain actions?

1 Answers1