- Once a user is authenticated, the first tier of access that they can be granted is to manage the operations of the server itself. These permission settings are grouped into server roles that the users can get assigned to. SQL Server includes three different categories of server level roles. These are: Fixed roles, that are defined by the server, the Public role, which is automatically assigned to every user, and User-Defined roles, which can be customized by the server administrator to meet specific needs. Let's take a look at each one. Fixed roles are predefined permissions that allow users to perform standard server maintenance tasks.
Fixed roles cannot be modified, but users can get assigned to multiple roles if their area of responsibility requires that they perform tasks that span categories. The Fixed roles within SQL Server include: bulkadmin, which grants permissions for the user to use the bulkinsert transact SQL commands to load data into the database, dbcreator, which can alter, drop, and create databases, diskadmin, which can alter, drop, and create disk files, the processadmin can stop running SQL Server processes, the securityadmin creates and modifies user log-ins, the serveradmin is able to configure server-wide settings and shut down the server, the setupadmin, who is able to configure linked servers, and finally the sysadmin, or system administrator, which has full permission to perform any task on the server.
The Public role is another fixed role, but it's automatically assigned to each user, and users can't be removed from the Public role. It provides a minimum amount of access to the server once authenticated, but nothing else. Finally, there are the User-Defined roles. User-Defined roles allow the server administrators to create their own packages of permissions in order to assign to users with specific needs. Since the fixed roles cannot be modified, User-Defined roles are typically created to provide some sort of subset of permissions so that the users can be granted permission to perform tasks, without allowing them to do more than they need to.
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