Creating SQL Server Login’s and assign specific roles to them

Introduction

Today in this article we will discuss on how to create Logins and give database level roles in SQL Server and how to assign these roles to the specific users.

So in this scenario we will create two logins one with admin role and one with readonly tester role.
So let’s start with starting the SQL Server Management Studio.

Step 1 - Start the SQL Server Management Studio and login into it using the admin credentials.

Step 2 -Expand the Security->Logins Section

Step 3 -Right Click on the Logins folder and select New Login option. Enter the login details and then click ok.

Step 4 - Similarly create another login named Tester.

Step 5 -Next expand the Database at which you want to create roles.

Step 6 - Next right click on Database roles and select new database role, give the role name, go to securables tab click on search, in the popup window select the All objects of the types option click ok, Select database as the object type and click ok. Now set the permissions to the role at the database level as shown below and then click ok

Step 7 -Now go back to the server logins double click on the created Tester login and goto user mapping tab select the db for which we have set up roles [SampleDB] then map the login to the tester role and click ok.

Step 8 - So we are done with the tester role and mapping the tester user to tester role.

Now similarly double click on the Admin login and goto user mapping tab , select the sample db, and select the inbuilt db_accessadmin and db_owner role and click ok.

Now just refresh the server. Login using the tester user and try to perform all the Select, create, insert and other operations and you will see that you will have only the select [readonly] permission on the sampleDB.

Same way login using the Admin user login and perform you will see that you can perform all the activities.
Thats it..

Hope this article will help you all ..