Create user and  manually grant privileges in MSSQL Server via TSQL

This tutorial will guide you through simple creation of user in the MS SQL server and giving that user privileges on specific tables. For example, you will learn how to grant SELECT privilege to user through TSQL.

Steps

Follow this steps:

Login with the admin account and go to Security -> Logins.

Create user in MS SQL Server and grant privileges on table through TSQL

Right-click and choose New Login. That will open the user creation & configuration window. 

Create user in MS SQL Server and grant privileges on table through TSQL

Create user in MS SQL Server and grant privileges on table through TSQL

Fill requested data - my uses login name is TestUser and I wanted it to be able to authenticate as just SQL Server user. The password you add here will be the initial password for the newly created user and he will be asked to change it after first login. Also, pick default database for your user.

Create user in MS SQL Server and grant privileges on table through TSQL

On the User Mapping section check databases that you want your user to have access to. This is very important step. Save and your user will be able to login. 

Create user in MS SQL Server and grant privileges on table through TSQL

The next step is to add some permissions to your user and this can be achieved manually through MS SQL Server Management Studio or through simple TSQL query. We'll try the second approach. First, you say on which database you want to add privileges (here it's AppIdent database). After that, you write GRANTs and then in the end keyword GO. For example, here we're granting SELECT and UPDATE on User table and SELECT, INSERT and UPDATE on the Document table.  

USE AppIdent
GRANT SELECT, UPDATE  ON [dbo].User TO TestUser
GRANT SELECT, INSERT, UPDATE on [dbo].Document TO TestUser
GO

As you can see, it's not complicated. You can find more details and topics on MS SQL Server's official documentation.

Check also...