From the course: Microsoft SQL Server 2019 Essential Training

Create a new user account - SQL Server Tutorial

From the course: Microsoft SQL Server 2019 Essential Training

Start my 1-month free trial

Create a new user account

“

- [Instructor] We can see what users have access to the server by expanding the security folder here in the Object Explorer window and then expanding the Logins folder. At the top we can see a number of service accounts that SQL server itself uses to communicate with its own components. At the bottom is my Windows user account, and this is the one that I'm currently logged in with, and then we also have the SA account. This is a SQL server authentication account. Let's go ahead and right click on the SA account and come down and choose Properties from the popup menu. This is where we can come to reset the password if we forgot what we put in when we installed SQL Server. You can either type in the new password and confirm the password in these two boxes here or if I blank both of these out and turn on this checkbox here that says enforce password expiration, and this checkbox that says user must change password at next login, that'll create a situation where the next time this person logs in they'll be able to create their own password. I'm going to turn both of these off again and put in that password again up here. Underneath the server roles page, which is the next page down, we can see the things that this user has permissions to do. Both our Windows account and the SA account are system administrator or sysadmins. This means that they have permissions to go anywhere and do anything on the server and within any database that resides on the server. So even though all of these check boxes aren't marked, the sysadmin box overrides everything. I have a whole chapter dedicated to the topic of security and user permissions towards the end of the course, so we're going to go into much more detail on the settings here on the server roles page as well as the settings that we can find on the user mappings page. Let's go ahead and switch down to the status page. Here's where we can grant or deny access into the database engine and we can even enable or disable the login entirely. If I disable the login and then press the OK button down here and then come over to the Logins folder in the Object Explorer and right click and choose Refresh, you'll notice that the icon updates here to indicate that it's disabled. We now get a red X on top of the icon. Now if I try and use this account by coming up here to the plug icon, that'll bring up the Connect to Server window and I try and use my SQL server authentication and my SA account here, I'll go ahead and type in the password and press Connect. And this time I'll get the message here, an error, saying that the login has failed. Let's go ahead and say OK to this and I'll press cancel here. And I'll re-enable the SA account by right clicking on it, going back into Properties, switching back to the Status page and enabling the login here. Once again, then I'll come back over to the Logins folder and simply refresh the view to update the icon. Now I'd like to create a new user account here in SQL server so we can start to get used to the idea of segmenting access to the system. In order to do that, we'll right click on the Logins folder and Choose New Login. That'll allow us to give the new user a name, I'll call her Octavia. I'm going to choose this as a SQL Server authentication mode and we'll give her a password. I'm going to leave these two check boxes on that enforce the password expiration and force the password to be changed the next time the user logs in. Then we'll take a look quickly here at the server roles and we'll see that Octavia is only going to be granted access into the public role, they're not going to be a sysadmin like our other users. Let's say OK and that'll create our new login, we can see it over here in the logins folder. And now if we try and login to server under Octavia's account, we'll see what it looks like. Once again, the server name will be the same that we've been using, the authentication type will be SQL Server authentication, the login name will now be Octavia, and we'll type in the password. When I press Connect, SQL Server Management Studio tells me that the password is expired and we need to enter in a new password before we can log in. So let's go ahead and give her a new password here. Once you have confirmed the password go ahead and press OK and now we're logged in underneath Octavia's account. We can see her view of the system here on the bottom portion of the Object Explorer. In fact, let me just scroll up here and I'll collapse my version here, which is this first one, and we'll just take a look at it here underneath Octavia's account. If we go back into the security folder and into the logins folder, you'll see that it looks drastically different from what we saw previously. Octavia's account is limiting her to only see the existence of her own account and the SA account and none of the other ones. So since it's a lot harder to create a fake person out of thin air with Windows authentication, experimenting with different user accounts within the SQL Server authentication protocol can give you a good idea on how the server will look and function when viewing it from the perspective of a user with limited permissions.

Contents