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.
Right-click and choose New Login. That will open the user creation & configuration window.
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.
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.
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.