Demo: Create a user in SQL Server

- [Joey] In order to follow along in this demo you're gonna need to have SQL Server running on your machine. Just a quick tip, if you've done a default install of SQL Server and not changed any of the configuration options, you'll have to go over here and right click on your server name and click Properties and go to Security and change the Server authentication mode from Windows authorization to SQL Server and Windows Authentication mode. That's what we refer to as mixed authentication. Otherwise when we try to log in as any of these secondary users, you'll get an error. Additionally, if you wanna follow the second half of the demo, you'll need to have an Azure SQL Database running with Azure Active Directory.

If you don't have all that, don't worry about it, you're just gonna see how it works. So the first thing we're gonna do here is create a new login from Windows. Typically you do this with a Windows Active Directory domain but here and probably on your own machine, I don't have one, so we're just gonna use the Windows machine for authentication. So when we create this login, car-booth-17-pc, which is the name of this computer, in liadmin, if someone is logged in to this specific machine, as liadmin and they wanna connect to this SQL Server, they'll be able to connect.

Just like I can see down in the bottom, I'm car-booth-17-pc Joey Dantonio. I'm logged into this machine as that and therefore I can authenticate to the SQL Server as that. So let's go ahead and create this login. And we've created that login, you'll note we haven't assigned any permissions or tied into any databases, but if we go over here to object explorer and we refresh our logins pane, we see that that user is added. We can double click on the user and we see, if wanted to map it to a user and a database, here we could. So we could give it db_datareader in our demo database.

And I'll go ahead and do that through the GUI there. That was how we would do that for a Windows user. And if you're Active Directory this wouldn't change. The only thing that would change would be the name of the directories. So instead of having car-booth-17-pc you'd have your company name .local or .com. And let's go ahead and create this log go for DemoUser. And we're gonna create it with a password. And I'm gonna switch to the Demo database. And I'm gonna go ahead and create a user for that login. So, we'll go ahead and login again.

And I'm gonna connect to the database engine and I'm gonna change this to SQL authentication. And now I can see that I've logged in there successfully and I can see here I'm logged in as DemoUser. And I see that I can see all the databases on the server, even though I have permission to only the Demo database. This is a little bit of quirk of SQL Server Management Studio, it's something a lot of DBAs have asked for, but your users will be able to see, even if they don't have permissions, like this, you can see AdventureWorks2016 is not accessible, but if I click on Demo, I can see the objects in there.

This is, like I mentioned, just a quirk of SQL Server. It's a fix that a lot of folks have asked for, but Microsoft has not done. That's how we create users and logins within SQL Server. What I'm gonna do here is switch our connection over to Azure SQL Database. You can see this is Azure SQL. I've logged in already with multifactor authentication, but this is called dsac.demo.database.windows.net. So here we are in Azure SQL Database. You'll note I'm connected to the master database and I'm gonna build this user in the master database.

If I wanna switch databases, within Azure SQL Database it's a little bit different than in traditional on-premesis SQL server. Where as on-prem I would use the use database command, so I would say use AdventureWorks, here I'd have to do my switch at the top. I'm gonna stay in master here, 'cause I wanna create this user in master, because of these roles. However, if I wanted to switch to a user database, I have to do it up there, or launch a new query from object explorer. Ideally, when you're doing development against Azure SQL Database, you wanna specify your database name in your connection string.

But here I'm gonna go ahead and create this user and it's monica@dcac.co from external provider. You'll note that's not from Windows, so it's a little bit different than you would do it, with SQL Server on-prem and Active Directory, but just the way it's coded here in Azure SQL Database. And then we'll go ahead and add these two roles to the database. These roles have already been created, so they're dbmanager and loginmanager. In here, when you look at the database now, and I'll refresh, and I can see that this user is created. And it's a little bit different than a Windows user, 'cause we see monica@dcac.co as opposed to dcac slash monica like we would on-prem.

Just things work a little differently with Azure SQL Database, but we still authenticate using Azure Active Directory. So it's similar, but slightly different. Conceptually it's very similar.