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

Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 9468 red flag
Rating: 5 out of 5  
 1 vote(s)

With this article we will learn how to create SQL server logins and roles and assign different roles to the users.


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 ..

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Madhu Rokkam
Member Level: Bronze
Member Status: Member,MVP
Member Since: 1/13/2011 3:13:20 PM
Country: India
Thanks and Regards Madhu

Login to vote for this post.

Comments or Responses

Posted by: Ankitwww on: 2/21/2011 | Points: 25
Good one!!
Now next time can you please explain about different roles and the differences between them.
Posted by: Madhu.b.rokkam on: 2/21/2011 | Points: 25
Sure Ankit.. I will try to brief on that .. Do post your requirements and comments like this and I will try to share them too. :)

Login to post response

Comment using Facebook(Author doesn't get notification)