SQL Server: Fixed Roles, Exception Handling Techniques, and Backup

This report provides a detailed overview of SQL Server, focusing on fixed server and database roles such as sysadmin, securityadmin, dbcreator, dbowner, dbaccessadmin, and dbsecurityadmin, explaining their functions and permissions. It further discusses exception handling techniques using TRY-CATCH blocks for problem identification and resolution. Additionally, the report emphasizes the importance of database backups, detailing different backup levels including full, incremental, differential, and daily backups, alongside common sources of database failures like hardware and software issues. This document is available on Desklib, a platform offering a wide range of study resources for students.

Running head: SQL SERVER
SQL Server
Name of the Student
Name of the University
Author Note

1SQL SERVER
Table of Contents
Question 1: Explanation of the following fixed-Server/Database roles:...................................2
i) Sysadmin:...........................................................................................................................2
ii) Securityadmin:...................................................................................................................2
iii) DB-Creator:......................................................................................................................2
iv) DB-Owner:.......................................................................................................................3
v) DB-Accessadmin:..............................................................................................................3
vi) DB-Securityadmin:...........................................................................................................3
Question 2:.................................................................................................................................4
Different types of exception handling techniques for problem identification:......................4
Appropriating actions for resolving problems:......................................................................5
Question 6:.................................................................................................................................5
Importance of Database Backup:...........................................................................................5
Different Level of Backups:...................................................................................................6
Common Sources of Database Failures:................................................................................7
References:.................................................................................................................................9

2SQL SERVER
Question 1: Explanation of the following fixed-Server/Database roles:
i) Sysadmin:
The sysadmin plays a fixed-server role in the SQL server. This actually is an admin
level role which covers all the other roles in the SQL server and consists nof unlimited scope.
By having this property, the sysadmin has become very much powerful in the environment of
SQL server (Delaney & Freeman, 2013). It is very much important to know which peoples
are having the role of a fixed server and this query can be made using the sysadmin as this is
having full control over the server.
ii) Securityadmin:
Like the sysadmin, the securityadmin also plays the fixed-server role in a SQL server
and can be considered equivalent to the role of the sysadmin. The members of the
securityadmin can manage the logins in the server and the properties of them (Masood-Al-
Farooq, 2014). The securityadmin can DENY, GRANT and can REVOKE the permissions in
the server level. With the server level permission members of the securityadmin can also
GRANT, REVOKE and DENY permissions in the database level if the members are having
access to the database. Another additional feature of the securityadmin is that SQL server
login passwords can be reset through it.
iii) DB-Creator:
As per the name, the DB-Creator allows creating a database in the server after a
successful login in the database. A normal user cannot be a member of this DB-Creator
(Ward, 2018). In many of the cases the DBA’s does not get full rights to the sysadmin, and in
this type of cases, the DB-creator is used for creating the database in the server. The DB-
Creator is very much useful for creating new databases without any intervention when the

3SQL SERVER
sysadmin is absent to grant the permission. The DB-Creator is an ideal role for the junior
DBA for controlling the SQL server.
iv) DB-Owner:
The DB-Owner plays the role of the fixed database in the SQL server. This type of
fixed database roles is stated at the level of database and in each of the database these roles
exist. The members of the DB-Owner are able to manage the fixed-database role membership
(Utami & Raharjo, 2014). By this, the members of the DB-Owners are able to do all the
activities related to the maintenance and configuration of the SQL database. The member of
the DB-Owner is also able to drop a specific database in the SQL server. Though the DB-
owner is related with the configuration and server maintenance tasks, it cannot perform some
of the maintenance activity. This type of maintenance activities are performed in the data
warehouse of the SQL server and require server-level permission.
v) DB-Accessadmin:
The DB-Accessadmin also plays the role of the fixed database within the SQL server.
The main role of the DB-Accessadmin members is adding and removing the accesses to the
SQL database for the logins from the Windows system, Windows group and for the server
logins of the SQL (Мамаев, 2015). The DB-Accessadmin can also handle the security
measures of the database. The DB-Accessadmin can grant, revoke and deny the permissions
which are essential for entry into the database. With the combination with DB-Securityadmin,
the total security of the database can be managed. The DB-Accessadmin is also handled by
the junior DBAs, and normal users of the database cannot be a member of this role.
vi) DB-Securityadmin:
The DB-Security admin role is similar to the role of the securityadmin role. The main
difference between these two is that securityadmin plays the role of the fixed server while the

4SQL SERVER
DB-Securityadmin plays the role of the fixed database in the concern of security of the
database. The DB-Securityadmin manages a membership role and also manages permissions
considering the securable of the SQL database (Zhao et al., 2014). Though the main role of
the DB-security is in the security purposes, it is used very little as in most of the cases DBAs
manage the securities. In some rare critical cases, the actual role of the DB-Securityadmin is
used.
Question 2:
Different types of exception handling techniques for problem identification:
For the identification of the problems, the SQL server uses two types of exception
handling techniques. These two techniques are the TRY block and the CATCH block
exception handling techniques that are used for the identification of the problems (Cacho et
al., 2014). The TRY and the CATCH blocks determine and catches all the errors related with
the execution having severity higher than 10 and which does not close the connection of
database. This mechanism of the TRY and CATCH block is mainly two code blocks which
store some of the procedures which are essential for executing some other codes. The two
section works in a specific way that when some errors are detected by the TRY block, these
errors are handled in the CATCH blocks (Lal & Sureka, 2016). Whenever there is no error is
detected by the TRY block the control passed to the statement instantly next to the statement
of the END CATCH. For the cases when an error has been detected within the TRY block the
control pass to the first statement within the CATCH block. In this case, if the last statement
is the END CATCH in the procedure, then the control is passed back to the stored procedure
statement. After the execution of the CATCH block, the control goes to the state next to the
statement of the END CATCH (Mistry & Misner, 2014). The errors which trap in the in the
CATCH block does not go back to the calling application. In many cases when the

5SQL SERVER
information needs back to the application, the CATCH block uses the mechanism of the
SELECT or RAISERROR and PRINT.
Appropriating actions for resolving problems:
The construction of the TRY CATCH is generally nested kind. In this type of
structure, either the CATCH block or the TRY block can contain the nested constructs of the
TRY CATCH block. The errors which are detected in the CATCH block are treated such as
that it is generated in somewhere else (Deng, Offutt & Li, 2013). Considering the execution
of the TRY block, it handles the errors in some specific ways. In the first case when the
stored procedure does not have any type of TRY CATCH construct error to return the control
to TRY block associated CATCH block which holds the statement of EXECUTE. In the
second case when the stored procedure does have a construct of a TRY CATCH, the error
returns the control to the CATCH block. After the execution of the CATCH block, the
control goes to the next after the EXECUTE statement which is a stored procedure.
Question 6:
Importance of Database Backup:
The backup of the database is very much important in the aspects of the business
needs. There are various reasons behind the database backup. The database backup provides
unrestricted access to the data. When the database is backed up to an online server, it
becomes available irrespective of location and time. Thus it helps in the business processes at
any time. Another main importance behind the database backup is the extra protection (Jukic,
Vrbsky & Nestorov, 2016). When the whole database is backed up regularly to a cloud
server, it provides some extra security regarding the data safety. When the main database is
corrupted or loosed then this backup very much useful for retrieving the data. Protection of
the customer data is very much essential for any organization. Thus by creating a backup of

6SQL SERVER
the database, the data regarding the customer can be stored safely (Zaman & Butt, 2013). The
data management systems can be costly in various cases for the organizations. In such of the
cases the database backup becomes very much helpful as this will help the organization to
overcome the costs of the data management system which can be very much expensive for
some start-up organizations.
Different Level of Backups:
For the backup of a SQL, the main types of database backups are the normal or a full
backup, incremental backup, differential backup and the daily backup. Each of these backup
techniques has their own advantage and disadvantage. Moreover, these backup strategies can
be used together for creating an utmost strategy of recovery. Thus it can maximize the
efficiency of the organization.
The first level of backup is the normal or the full backup. When the normal backup
starts its execution, it backs up all the files of the targeted database of the location. This
backup includes all the system files, user files and the application data. All the targeted files
copied into a selected destination and then the archive bits are cleared.
In the next, another type of backup is the incremental backup. Since backup is a long
process and takes a huge amount of time taking the backup in a smarter way is the essential
solution for the business (Xu et al., 2015). This type of incremental backup only runs once
per a week. Also, only those files are backed up which have been created new or have been
updated since the last backup. That means whenever a file will be backed up. It will be not
backed up further until any changes occur in the file. Thus the backup actually becomes
lighter and takes a very less amount of time.
The differential backup is similar with the incremental backup but actually simpler
than the incremental backup. It is simpler than the incremental backup in the sense that the

7SQL SERVER
backup restore process is less critical than the incremental backup restoration method. The
main difference between the incremental backup and the differential backup is that the
differential backup only targets those files of the database which are modified since the last
normal backup, but this backup does not clear any type of archive bit.
The last level of the data backup is the daily backup. This type of backup is less
common in the database backup system. In this type of backup system, only the critical
operational files are taken into backup consideration. In this backup system, all the modified
files backed up continuously. Generally, this types of backup include the backup technique of
determining the timestamp of a file.
Common Sources of Database Failures:
There are several types of database failures when considering the database processing.
In this, some of the failures affect the main memory while the others affect the secondary
storage. The main two common sources of database failure are the hardware failures and
software failures.
The hardware failure includes the memory errors in the database. This memory error
arises due to the bad disk sector, disk crashes and due to the disk full errors (Wang et al.,
2015). Hardware errors are considered under the design errors, poor quality during the
process of fabrication and overloading issue of the mechanical parts.
The software failure arises in the system due to the issues in the software. The
software issue includes crashing of DBMS software, operating system, an application
program and more.
Another type of database failures is the system crash. The system crash can occur due
to both hardware and software failure. This type of failure occurs due to when the hardware

8SQL SERVER
part or the software part of the stop proper functioning and exits from the system. This type
of failures can crash the entire data of the database permanently.

9SQL SERVER
References:
Cacho, N., Barbosa, E. A., Araujo, J., Pranto, F., Garcia, A., Cesar, T., ... & Garcia, I. (2014,
September). How does exception handling behavior evolve? an exploratory study in
java and c# applications. In Software Maintenance and Evolution (ICSME), 2014
IEEE International Conference on(pp. 31-40). IEEE.
Delaney, K., & Freeman, C. (2013). Microsoft SQL Server 2012 Internals. Microsoft Press.
Deng, L., Offutt, J., & Li, N. (2013, March). Empirical evaluation of the statement deletion
mutation operator. In Software Testing, Verification and Validation (ICST), 2013
IEEE Sixth International Conference on (pp. 84-93). IEEE.
Jukic, N., Vrbsky, S., & Nestorov, S. (2016). Database systems: Introduction to databases
and data warehouses. Prospect Press.
Lal, S., & Sureka, A. (2016, February). LogOpt: Static feature extraction from source code
for automated catch block logging prediction. In Proceedings of the 9th India
Software Engineering Conference (pp. 151-155). ACM.
Мамаев, Е. (2015). MS SQL Server 2000.
Masood-Al-Farooq, B. A. (2014). SQL Server 2014 Development Essentials. Packt
Publishing Ltd.
Mistry, R., & Misner, S. (2014). Introducing Microsoft SQL Server 2014. Microsoft Press.
Utami, E., & Raharjo, S. (2014). Database security model in the academic information
system. International Journal of Security and Its Applications, 8(170).

10SQL SERVER
Wang, Y., Wei, X., Zi, Y., & Tsui, K. L. (2015). In situ measurement of seeking speed and
seeking induced head-disk interface instability in hard disk drives. Shock and
Vibration, 2015.
Ward, B. (2018). Security in SQL Server. In Pro SQL Server on Linux (pp. 317-367). Apress,
Berkeley, CA.
Xu, Q., Zhao, L., Xiao, M., Liu, A., & Dai, Y. (2015). YuruBackup: a space-efficient and
highly scalable incremental backup system in the cloud. International Journal of
Parallel Programming, 43(3), 316-338.
Zaman, M., & Butt, M. A. (2013). Enterprise Data Backup & Recovery: A Generic
Approach. International Organization of Scientific Research Journal of Engineering
(IOSRJEN), 2278-4721.
Zhao, G., Lin, Q., Li, L., & Li, Z. (2014, November). Schema conversion model of SQL
database to NoSQL. In P2P, Parallel, Grid, Cloud and Internet Computing
(3PGCIC), 2014 Ninth International Conference on (pp. 355-362). IEEE.