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
• Importing SQL Server Analysis Services (SSAS) Sample CUBE and Processing the CUBEs
• Configure SQL Server Analysis Services (SSAS) in SQL Server 2012 Step by step guide
• Install and configure SQL server reporting services (SSRS) in SharePoint 2013/2016
• Killing a SPID in SQL Server and Checking Rollback Progress
• Windows successfully diagnosed a low virtual memory condition sql server event id 2004

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.