Create a user and give permission in SQL Server

This SQL server tutorial, we will discuss how to create a user and give permission in SQL server 2014. First, we will add the user to the SQL server 2014 and then we will assign very permissions to the user.

Create a user in sql server

Now we will see how to create a user in SQL server 2014. First Open SQL Server Management Studio. And then expand Security. Then right-click on “Logins” and click on “New Login…” like below:

Then in the Login – New dialog box, give the user name and click on “Search…” button. In the Select User, Service Account, or Group dialog box, enter the user name and click on Check Names, it will resolve the user. Then choose the authentication as “Windows authentication”.

Add Server Roles to the User in SQL Server

Then we will add Server roles to the user. When you will open the Server Roles tab, you can see by default the role granted as “public” like below:

Here you can check the server roles you want to provide like below:

Assign Database Role membership to various databases to SQL Server User

Now we will see how we can assign database role membership to varous databases like master, model and tempdb.

Click on the “User Mapping” and then select a database like “master” and then check the checkboxes like “db owner”, “db securityadmin”, “public” etc like below. The same way we can do this for all other databases like model and tempdb etc.

Then click on OK, to save the changes.

You may like following SQL server tutorials:

• Download & Installation SQL Server 2016 and Management Studio 2017
• Difference between primary key and unique key in SQL Server
• Importing SQL Server Analysis Services (SSAS) Sample CUBE and Processing the CUBEs
• How to Setting maximum memory in SQL server?
• Create Configure and Use SQL Server Database using Microsoft Azure
• How to create SQL Database in Microsoft Azure Portal
• Delete and create a new log file with minimum size in SQL Server
• Enable remote connections for SQL Server
• Sequence in SQL server
• SQL Server joins tutorial
• SQL Server Functions Tutorial
• SQL DML and DDL commands

In this tutorial, we learned how to create a user in SQL Server as well as how to assign various roles to the user in SQL server.