From the course: SQL Server 2016: Administer a Database Infrastructure

User options for Azure SQL Database - SQL Server Tutorial

From the course: SQL Server 2016: Administer a Database Infrastructure

Start my 1-month free trial

User options for Azure SQL Database

“

- [Instructor] Creating and managing user accounts in an Azure SQL database is very similar to working with a local version of SQL Server. This can be accomplished either within the Azure portal or using a connected application like SQL Server Management Studio. Let's go ahead and take a look here in Azure first. Now to begin, I need to make sure that I have an Azure database provision. I already have one provision called Azure SQL database and it's here inside of my dashboard. If you don't have a database to work with though, go over to the new button. In the market place you'll come down to databases. And then under our featured apps, you'll choose SQL database. That will allow you to fill in all of the details and then press the create button. Now since I already have one I'll go back to the dashboard and I'll choose it from the menu. That'll take us to the overview page for our database. Now before we get too far, I want to set the server firewall. That will allow us to connect to SQL Server Management Studio in just a moment. To do that, I'll press the Set server firewall button on the top. Then I'll press the Add client IP address to add our current IP to the list. I'll press the save button. And the server firewall settings will be updated. I'll go ahead and close this to return back to the overview page. Now we can start issuing transact SQL codes to the server. To do that, I'll go to the tools menu. Then we'll choose the query editor. And at this point, if you're prompted, go ahead and agree to the terms of use by placing a check in the box and pressing the okay button. Before we can issue a command, we need to log into the server. So I'll press the log in button. We'll choose the SQL Server Authentication method. And then provide the log in name and password that were established when we created this database. I'll press the okay button and it should authenticate me just fine. Now we can start issuing transact SQL commands. I want to create a new user and this is going to be a contained user account. This is one that doesn't have a SQL Server log in attached to it. To create a contained user we use the Create User command. And then followed by the name of the user in which case, this is going to be Tabitha. We'll give Tabitha a password so with the With Password command. And then equal sign and in single quotes her password. Make sure it's a strong one, so I'll say capital T AB one THA nine. And then a closing single quote. I'm going to run this and then Tabitha's account should be created. Now just like we saw previously in Management Studio we could issue a Select User Underscore Name command in order to get the server to tell us who it currently thinks that we are. Right now, it thinks that we are the DBO or the database owner. We can execute commands as Tabitha by using the Execute AS User command. And then saying we want to impersonate Tabitha. Then we'll select user name again. When I run this command, the server now thinks that we're Tabitha. So that's how we can start making contained users here inside of Microsoft Azure. Let's take a look at how this looks inside of SQL Server Management Studio. To do that, we need to go back to our overview page for the database. I'm going to click on the database name. And on the overview tab we're going to find the server name. This is the fully qualified server name that we use to log into from SQL Server Management Studio. When I hover my mouse over it I can click on this little copy button over on the right and copy it to my clipboard. Then, we'll switch over to Management Studio. To log into the Azure database we first need to disconnect from any databases that we might be connected to. And then press the connect button. The server name is no longer going to be the local server. We'll go ahead and paste in the fully qualified name that we just copied to our clipboard. The authentication method will be SQL Server Authentication. And we'll give it the log in name and the password that was previously established. I'll press the connect button and that'll connect me to my Azure SQL database. And I can see it right here. If I expand the security folder and then expand users we should see Tabitha's account. Now you can start issuing commands here inside of Management Studio. Let's go ahead and open up the exercise file for this movie. It is 0204 user Azure dot SQL. These commands at the top are the ones that we've already issued inside of Azure so let's go ahead and skip down the line to line number 14. First, I'm going to carry in a couple of tables, we'll create a table called new products and a table called new vendor. Then, I want to give Tabitha some permissions. Let's go ahead and grant select on new products on Tabitha and we'll grant insert on new products on Tabitha. Now we can execute as user Tabitha and we'll select the user name to make sure that we know who we're being treated as. And in fact, we are being treated as Tabitha now. Let's see if she can select anything from the new products table. And she can. Let's go ahead and see if she can select everything from the new vendor table. And she cannot. Remember, we didn't grant select permissions on the new vendor table up above. Just select and insert on new products. Let's go ahead and scroll down a little bit and try and insert a product. And that works as expected and we can select everything from a new products table again just to be sure. Now because Tabitha is a contained user we can actually log in to the server as Tabitha. Instead of just trying to impersonate her with the Execute as user command. To do that, I'll right click anywhere in the open space and go to connection and choose disconnect. Then I'll go to connection again and choose connect. This time instead of my log in name I'll type in Tabitha. The password we established earlier was capital T-A-B the number one, T-H-A and the number nine. Let's go ahead and connect again. And this time, instead of executing as user Tabitha I'll just run this slide and it says, select user name. And we can see down in the results window that SQL server is treating us as Tabitha and we can also confirm that by taking a look here at the name up in the tab. So as you can see, creating and managing users in an Azure SQL database uses the same transact SQL commands that we've already been using in the local version of SQL Server. The only difference is the fact that you'll almost exclusively be creating contained user accounts without a corresponding log in for the server. Other than that permissions and group or role memberships are handled in exactly the same way.

Contents