Join Adam Wilbert for an in-depth discussion in this video Solution: Creating a new user account, part of Database Foundations: Administration.
(bright, crisp music) - I hope you were able to create a new login for Angela. Here's how I'd go about setting up her account. First, I want to make sure that I've logged into SQL Server with an account that has the appropriate permissions in order to create a new user login. So I'm just going to use the Windows authentication with my user name here, which I know is a system administrator. So I'll connect to the database server that way. Then we'll come into the Security folder, and right click on Logins, and choose New Login. The Login Name is just going to be Angela's name, so "Angela," and her last name is "Hashton." We're going to set this up as a SQL Server authentication, and I'll provide a temporary password.
I'll just type in "temp" right here, because I know that Angela is going to change this as soon as she logs in. So the password is just "temp" for now. And I know that she's going to change it based off of this checkbox right here. So "User must change password at next login." And that just makes it so that Angela provides her own password, and it's probably going to be something that I don't even know about, so that's good. I'll leave this check mark on, and I'll leave the other two on as well, so that way we're enforcing the password policy that is currently in place on this computer. Next we need to assign Angela to the server roles, so I'll switch over here to this page here on the left.
We want to make sure that Angela can create new databases, so that's the "dbcreator" server role. And we also want Angela to have the ability to modify the server settings and shut down the server, so that's going to be the "serveradmin" role here. So I'll make those two changes here, I'll change those two check boxes. Go ahead and say OK to that, and Angela's new login has been created, and it's right there. Let's go ahead and log off and log back in and see what it looks like from her perspective. When I switch over to SQL authentication here and I type in the name "Angela Hashton," I'll go ahead and type in the temporary password, which is conveniently just "temp," and say "connect." It's going to tell me that the password is expired.
I need to provide a new password here, so I'm going to just type in "Angela," which is a terrible password, I'll admit that. But go ahead and type in whatever password you like, something that's a little more secure. Go ahead and type that new password in and confirm it. And say OK. And now Angela has access to the database server. She can go in and create new databases here, or she can go ahead and also shut down the server and perform some other tasks. So that's how I would go about creating the new login. I hope you were able to come up with a similar solution.
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