From the course: Developing Microsoft SQL Server 2016 Databases

Add a user to a role with T-SQL - SQL Server Tutorial

From the course: Developing Microsoft SQL Server 2016 Databases

Start my 1-month free trial Buy for my team

Add a user to a role with T-SQL

“

- [Instructor] Adding users to Roles is a good way to manage the permission levels that each user has on objects of the database. I want to take a look at an example of creating a new user role and assigning some permissions and a user to it, in order to limit their access to some sensitive information. We'll start by creating a new database called User Roles Example, then I'm going to create a table to hold information about our employees. It'll include an employee ID field, an employee name, employee phone, and some employee salary information. Next, we'll insert a couple of values into this employees table. We'll insert a row for Malcom and his phone number and his salary information, Rory, and Brianne. Let's go ahead and highlight lines 16 through 20 and execute those. Next, just to make sure everything worked out okay, I'm going to go ahead and run the select statement, to select everything from the employees table. That'll display the results down below. Now while I'm storing employee ID and salary information in this table, I'd like to limit access to this data. I can do that by first creating a view that doesn't pull out these columns from the table. Let's go ahead and scroll down a little bit and we'll find the lines that'll create our new view of the data table. The view is going to be named Employee Phone Numbers and it's only going to pull out the employee name and employee phone columns from the employees table. Then, if I run the select statement against the employee phone numbers view, we'll see just those two columns appear down in the results. Next, we need to create a new user account. Before we can add a user to this database, they actually need to exist at the server level first. So, I'm going to come over to object explore and open up the security folder and then go and right-click on logins and choose new login. We'll create a new login name called Jamie Temp and since Jamie Temp isn't actually a real person, I'm going to choose SQL Server authentication and provide a password here. I'll just type in ABC123. Obviously, if we were creating a real user account, we would want to use a much stronger password. I'm also going to uncheck the Enforce password policy check box, that'll automatically deselect Enforce Password Expiration so that the first time we log in as Jamie Temp it's not going to prompt us to create a new password. With the new log in name created, we'll press the OK button and that user will get added into the server log in permissions and you can see them right down here. Next, we need to add this log in account as a user of this particular database. So now I can come back to our script and create the user Jamie Temp, which will apply it to the User Roles Example database. Now it's time to create our role. The first step is to actually create the role, so it'll be a simple Create Roll and the name of the role which I'll call View Only. Next we need to assign some permissions to the role. I want to revoke select permissions on the employees table to the view only role. I'll go ahead and highlight lines 43 through 45 and execute those, but I do want to grant select permissions on the employee phone numbers table. We're going to grant those permissions to the view only role. We'll go ahead and highlight 47 through 49. Finally, we need to assign the new user that we just created, Jamie Temp, and we need to add that into the view only role. We can do that by altering the role, we'll say alter role view only, and we'll add the member Jamie Temp. Now everything is set up and we need to check Jamie's permissions. Let's go ahead and scroll down just a little bit further on our script. In order to log off of the server, I'll just right-click anywhere here in the blank space of the tab and in the pop-up menu I'll come down to Connection and choose Disconnect. That'll change the tab on the query editor window to not connected. Then I'll just right-click again, go to Connection, and choose Connect. This time, instead of signing in as my normal user name, which would grant database owner permissions across the whole system, I'm going to change this drop-down menu that says Authentication and I'll change it to SQL Server Authentication. Then we'll type in the log in name of Jamie Temp and the password that we provided, which for me was ABC123. I'll press Connect and that'll reconnect us to the server underneath Jamie's new login account and you can see that across the tab. Now that we're back in here, we can go ahead and see what Jamie can do. First, I want to make sure that we're still using the User Roles Example Database. I'll go ahead and highlight these two lines and execute them, then I want to select everything out of the employee phone numbers view. I'll run these two lines on 59 and 60. So it looks like Jamie Temp can successfully access the employee phone numbers view. Let's go ahead and run this statement, Select Star from the employees table. This time we get an error message saying that the select permission was denied on the object employees. So it looks like our user roles are working as intended. In order to clean up the server and actually delete the user roles example database, when we're done working with it, I'll right click and go back to Connection and Disconnect. That'll disconnect Jamie Temp. I'll right-click, Connection, and Connect and this time I'll log back in as myself using Windows Authentication. Then, we'll run these four lines here to remove the User Roles Example Database from the system and just to clean everything up I'll come over to the log ins folder, underneath security and I'll delete that new user that we just added. Just right-click on it, choose Delete, and then press OK. That's how you can begin to leverage user roles to manage permissions with your SQL Server databases. Once a user or a group of users is assigned to the role, you can grant or revoke permissions in Mess.

Contents