In order to work within the server and gain access to the various databases and tables of information that are being stored, Adam uses a program called SQL Server Management Studio, which is often referred to by its acronym, SSMS. It will provide a graphical interface to the server’s content.
- [Instructor] In order to work within the server, and gain access to the various databases and tables of information that we're storing, we'll use a program called SQL Server Management Studio. Management Studio is often referred to by it's acronym, SSMS, and it'll provide a graphical interface to the server's content, so that we don't have to rely on a text-based interface like the one we saw using the SQL command window during the installation process. I've added a shortcut to Management Studio down here on my start window. So I'll go down to this button to launch it. When it starts up, I'm presented with this login window.
Now first, I'm going to confirm that we're logging into the Database Engine using this drop-down menu here. My other options are to login into Analysis, Reporting or Integration Services, but I'm just going to leave it on Database Engine. I'll also verify the name of the server that I'm going to connect to, right down here in the second box. The format that this takes is the name of the computer, followed by a backslash and then the name of the server instance. In my case, it's a server called SQL Express on the computer CAR-BOOTH-04-PC. Next, we need to provide our login credentials down here.
SQL Server manages the users that connect to it in order to safeguard the data within. Some users, called administrators, will have the ability to alter anything that they wish. Other users may be granted a small subset of permissions, such as the ability to add data to a table, but not create new tables. Or the ability to read data only, but not to alter any data. Because of this, SQL Server needs to know who we are in order to assign the correct permission levels. It does this by supporting several different authentication methods. When we installed SQL Server, it automatically established the current Windows user as the database administrator using a method called Windows authentication.
By using this drop-down menu, we can see that the other authentication methods are SQL Server Authentication and Active Directory Authentication. With SQL Server Authentication we can establish users and roles within the server itself and allow it to manage their login names and passwords. Or we can use Active Directory accounts which is a login system typically used by larger organizations and companies. In order to gain access to the server's content, I'm going to make sure that the authentication method is set to Windows authentication. That will automatically populate my current user name that's logged in to this particular computer.
With my user name filled in now, I can go ahead and press the Connect button to connect to the server. Once I'm authenticated, I'll be presented with the name interface in Management Studio. You can expand the Object Explorer window which is this window over here on the left-hand side of the screen, by clicking over here on the right-hand edge and dragging it open. At the very top of the screen, you'll see the name of the instance that we're connected to, the version of SQL Server that we're using, and again, the name of the user that's connected to the server. If I expand this open a little more, we'll see my full name there. Let's go ahead and shrink this back down.
Now, we'll take a look at the Management Studio interface more in-depth in the next chapter, but for now, go ahead and click on the plus next to Security, to expand this object. Then we'll expand the folder called Logins. Here we can see a listing of all the different login accounts that are currently enabled on this system. Let's go ahead and expand this a little bit more so we can see the full names. Here's my current user account right here, and another one that I want to focus on is this one down here, called the sa account. SA stands for system administrator and it's one of the default accounts that comes with SQL Server.
This little red icon here, the downward-pointing arrow on this icon, indicates that it's currently disabled. In order to get to the properties for this account, go ahead and just right-click on it and go down to Properties. On the Login Properties for the sa account, I'm going to come down to the Status page, and we'll find this login property. We can see it's currently disabled, and I'm going to switch this over to enabled. Then, I'll go back up to the General tab and we'll give it a new password that we can remember. We'll go and highlight the current password and type in something new. I'll come down to the next box by pressing the Tab key, and I'll confirm that same password again.
This password can be anything that you like, but for security purposes, it should follow the strong password guidelines of including uppercase and lowercase letters, digits, and symbols, and be more than eight characters in length. Once you've verified the password, go ahead and press OK here down at the bottom of the screen to create that login account. Now if your icon didn't update and it still includes this red arrow here, just like mine did, go ahead and right click on the Logins folder and choose Refresh and you should see that icon updates to indicate that this is no longer a disabled account. Now there's one additional change that we need to make to this server and that's to enable the SQL Server Authentication mode, which is disabled by default, when the SQL Server is installed using the basic method that I used previously.
To do that, right click on the instance name up here at the top of the Object Explorer window and go down to Properties. Then click on the Security page and come down here to this option that says SQL Server Authentication and enable this option, SQL Server and Windows Authentication mode. That will allow us to login into the server using our Windows authentication name, such as mine right here, or the sa account, which is a SQL Server authentication method. Go ahead and press the OK button to save that change. And we'll see this dialog box that says, Some of our configuration changes will not take effect until SQL Server is restarted.
Go ahead and press OK to that and we'll go ahead and restart the server. Let's close Management Studio. I'll come down to the Start menu, and then we'll start up SQL Server Configuration Manager. I'll say, yes, to allow the app to make changes to my computer. Then we'll click on SQL Server Services. I can right click on the server instance, go down to Properties, and then press the Restart button to restart the server. After that's complete, go and press the OK button, and we'll close Configuration Manager and go back into Management Studio.
Now we should have two methods to login. Let's go ahead and try out that sa account. Instead of Windows Authentication, I'm going to change this drop-down menu to SQL Server Authentication. Then it's going to prompt me for a login name and password. The login is just simply sa, for the sa account. Then we'll type in the password that we previously established and press the Connect button. If everything worked out correctly, then we should get back into the server. If I expand my Object Explorer window, we should see that the name on the account that we're currently logged in to is now sa, instead of our Windows username.
So that's how you gain access to the server. You'll either use your current Windows or Active Directory account or you'll have to establish users within SQL Server. Using strong passwords and limiting the user accounts helps protect the server from unauthorized access in order to keep your data secure.
This course introduces the most important features of SQL Server 2016, and is designed to act as the student's very first look at how the server operates. Adam Wilbert shows how to install a local copy of SQL Server; demonstrates the working environment, SQL Server Management Studio; and walks students through their first hour creating tables, defining relationships, and writing and running queries with Transact-SQL commands. Everything you need to accomplish to create your first database is covered in just 80 minutes. Start watching to get up and running with this professional-grade database management system.
- What is SQL Server?
- Configuring the server
- Creating a new database and data tables
- Joining tables with keys
- Creating a SELECT statement
- Writing an UPDATE statement
- Adding data with the INSERT statement