There was a requirement to create a new user who would have only insert permissions to one table, this user would also have insert and select permissions to another table.

This is pretty simple to accomplish. First create this simple database with two tables

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE DATABASE TestPermission
GO
 
USE TestPermissions
GO
 
 
CREATE TABLE  TestAccess(id INT)
INSERT TestAccess VALUES (1)
GO
 
CREATE TABLE  TestAccess2(id INT)
INSERT TestAccess2 VALUES (1)
GO
CREATE DATABASE TestPermission
GO

USE TestPermissions
GO


CREATE TABLE  TestAccess(id INT)
INSERT TestAccess VALUES (1)
GO

CREATE TABLE  TestAccess2(id INT)
INSERT TestAccess2 VALUES (1)
GO

Create the new user

T-SQL
1
2
3
4
5
6
7
8
USE [master]
GO
CREATE LOGIN [SomeTestUser] WITH PASSWORD=N'TestPAss', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [TestPermissions]
GO
CREATE USER [SomeTestUser] FOR LOGIN [SomeTestUser]
GO
USE [master]
GO
CREATE LOGIN [SomeTestUser] WITH PASSWORD=N'TestPAss', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [TestPermissions]
GO
CREATE USER [SomeTestUser] FOR LOGIN [SomeTestUser]
GO

Now just give the user insert permissions to the TestAccess table

T-SQL
1
2
3
4
5
USE TestPermissions
GO
 
 
GRANT INSERT ON TestAccess TO SomeTestUser
USE TestPermissions
GO


GRANT INSERT ON TestAccess TO SomeTestUser

Login as the newly created user and try to run the following

T-SQL
1
SELECT * FROM TestAccess
SELECT * FROM TestAccess

Here is the error message that you will get

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object ‘TestAccess’, database ‘TestPermissions’, schema ‘dbo’.

Running the insert statement is no problem

T-SQL
1
INSERT TestAccess VALUES (1)
INSERT TestAccess VALUES (1)

Go back to the admin connection and run the following

T-SQL
1
GRANT SELECT,INSERT ON TestAccess2 TO SomeTestUser
GRANT SELECT,INSERT ON TestAccess2 TO SomeTestUser

As you can see you can combine privileges with the GRANT statement, you don’t have to do the separately

You just gave insert and select permissions to SomeTestUser for the TestAccess2 table

Now if you go back to the connection, you can run the following without a problem

T-SQL
1
2
INSERT TestAccess2 VALUES (1)
SELECT * FROM TestAccess2
INSERT TestAccess2 VALUES (1)
SELECT * FROM TestAccess2

In general you probably want a user to have read permissions or write permissions for all the tables, in that case you can use a role. The following will give read and write permissions for all the tables in the database

T-SQL
1
2
3
4
5
6
USE [TestPermissions]
GO
EXEC sp_addrolemember N'db_datareader', N'SomeTestUser'
GO
EXEC sp_addrolemember N'db_datawriter', N'SomeTestUser'
GO
USE [TestPermissions]
GO
EXEC sp_addrolemember N'db_datareader', N'SomeTestUser'
GO
EXEC sp_addrolemember N'db_datawriter', N'SomeTestUser'
GO