Join Adam Wilbert for an in-depth discussion in this video Securing the database server, part of Database Foundations: Administration.
Server level security begins with authenticating users. We make use of logins and passwords every day to access everything from our library records to our bank accounts. Typically, we do this as an end user and we don't have much control over the type of account access we're granted. When administering a server though, you get to determine who can go where and who can do what. Computer systems rely on identifying individual users in order to grant the appropriate level of permissions, and even though we've some dramatic improvements in facial recognition, fingerprint scanning, and other types of biometrics, the standard way that most systems authenticate a user identity is still through a username and matching password.
SQL Server offers three different ways that users can be identified by the server. First is the Windows user login that you use when you login to your desktop computer. These credentials use your Windows account to identify you as a user. Second is through your membership in a Windows user group. In some organizations, Windows accounts are assigned permissions through group assignments, and those groups can be granted permission in SQL Server as well. If the group is given access to a securable, then members of the group will automatically be granted the same level of access.
Finally, there's the ability to create SQL Server logins. This is also known as mixed mode authentication, and it allows you to create specific user names and passwords right inside of SQL Server, and is completely independent from any account that you might be using with the Windows operating system. Way back in the first course of this series, when we installed SQL Server Express, you might recall that we set up two different user accounts. The first was for the current windows user. The second used mixed mode authentication, and we created an SA, or a system administrator account.
The SA user is an example of SQL Server specific logins. Once we've created the user account, we need to assign fixed server roles to the user. These roles grant users the permissions necessary to perform server related administration tasks. Users can belong to several server roles, and there are many; including the sysadmin role, which allows a user to perform any activity on the server. Both the SA account and the original Windows user account have been assigned to the sysadmin role when we installed SQL Server Express. The dbcreator role will allow users to create, alter, drop, and restore databases, but not many users or perform higher level server related tasks.
And finally, the securityadmin role manages logins and their properties. We'll look at these, and other server roles, a little bit more in the next chapter. For now, just know that once a user is authenticated by the server, they can be granted different levels of access to perform tasks on the server itself. This is even before they're granted access to any actual databases, or database objects.
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