Join Adam Wilbert for an in-depth discussion in this video Creating a new Windows user, part of Database Foundations: Administration.
- In order to explore the Windows authentication method of logging into SQL Server, we'll first need to create a new Windows user on our computer. In order to do that, I'm going to come down to my start button here and right click on it and choose from the menu control panel, or I could go up to computer management. If we go through the control panel, we'll go through system security, we'll come down and choose administrative tools, and then up here to computer management there. In the panel over here on the left, I'm going to find the local users and group section, and we'll twirl that open and find the users folder here.
This is a list of all of the different users that are currently gaining access to my computer. In order to create a new user, I'll right click and choose new user from the menu. Here, we get to define the user name. I'm going to create a new log in account for Henry Twill. I'll say the user name is Henry Twill and the full name will be Henry Twill as well. Next, we'll supply a password that Henry will use when he logs into the computer. I'm going to type in a very simple password. You should use something that's a little bit more secure than this.
Let's go ahead and also turn this check box off that says the user must change the password at next login, that way when Henry logs in , it'll just use this password and he won't be forced to change it. Let's go ahead and press the create button and we'll have a new user account created for Henry Twill. Then I'll press the close button to get out of the new user dialogue box and we'll see Henry's account has been created right here. Let's go ahead and close this computer management window and return back to Management Studio. Now I'm still logged in as my current user here, Adam Wilbert which has the system administrator role.
I can go ahead and create new users on my instance of SQL Server by coming down to the security folder and opening the logins folder. I'll right click and choose New Login. Then we can supply the new login name for Henry. So first we need to supply the domain which is the name of the computer account and this would be the same domain as your current user account as a system administrator. We'll follow that with a backslash and then the username that we just created which was Henry Twill. I'll leave it as a Windows authentication login and press the OK button down here on the bottom.
Now we've got a new user named Henry here and we can go ahead and log out of our server and try logging in as him. I'll press the reconnect button here and when it's on Windows authetication, you'll notice that I don't get the ability to change my username here. It's locked into the current user that's logged into the computer. Unfortunately, we can't easily test Henry's new account. Because the login is being handled by Windows, we'd have to log out of our Windows session and log back into Windows using Henry's credentials before we could log into SQL Server as Henry.
Because I'm still logged into Windows as my own account, It's only allowing me to connect using the Windows authentication under my own account. This demonstrates one of the security benefits of using Windows authentication to manage user access to the database server. For this reason, we're going to work with local user accounts for the rest of this course. This will give us the opportunity to set permissions and then pretend to be those individuals by logging in as them. For now, let's just go back and remove Henry's accounts from SQL Server and for Windows. I'll press the connect button to connect using my own account which has system administration permissions.
We'll go down here to the security folder and logins; and we'll find Henry's account here. I'll right click on it and choose delete from the menu. Press OK to delete Henry from this instance of SQL Server; and I'm going to get this warning message here saying the deleting Henry doesn't actually delete him from Windows it just removes him from this instance of SQL Server. So let's go ahead and say OK to that. Then i'll go back out to the Windows computer management and remove him there as well. Back under local users and groups, we'll find the users folder and then we'll find Henry's account here.
I can just right click on it and choose delete from that list as well. We'll get one more warning message saying that we're going to to permanently delete Henry from our computer and that we can't restore him. Let's go ahead and make sure that we say yes to that and we'll permanently remove Henry from our computer. Typically user accounts are going to be setup by your network administrator for your organization or be created as actual logins tied to real life people on a Windows computer. In a testing environment like what we're working with, it gets to be a little more difficult to explore the database management system using Windows authentication. But that just speaks highly of the robustness of the security model when using Windows authentication.
Since it's rather inconvenient and difficult to trick the system into thinking that you're different users.
Note: This course will also prepare certification candidates for the "Administer a database" domain of the Microsoft Technology Associate (MTA) Exam 98-364, Database Administration Fundamentals.
- Securing the database server
- Understanding Windows authentication vs. SQL Server authentication
- Assigning fixed server roles and fixed database roles
- Granting object-level permissions
- Understanding ownership claims
- Creating backups
- Restoring a database