SQL Server Authentication and Exception Handling: A Practical Guide

This project explores the fundamentals of SQL Server authentication and exception handling. We create multiple authenticated users with specific roles and permissions, demonstrating how to manage database access effectively. The project also delves into the implementation of custom exception handling techniques, showcasing how to identify and resolve errors during data insertion operations. Through practical examples and detailed explanations, this project provides a comprehensive understanding of these essential SQL Server concepts.

Contents
INTRODUCTION...........................................................................................................................................2
QUESTION 1: Complete the following tasks................................................................................................3
Task-01: Create the users mentioned in above Table-1 for the database using SQL server
authentication with respective roles.......................................................................................................3
Task-02: Explain the following fixed server/database roles that exist in the SQL Server:........................7
Task-03: Explain the Policy-Based Management in SQL Server 2008 and perform the following tasks:..9
Create a SQL query for user Alice and give ‘Select’ permission for your table....................................9
Create a SQL query for user Dara and give ‘Control’ permission for your table..................................9
Create a SQL query for user Isabelle and give ‘Alter’ permission for your table..................................9
Assign one of the built-in server roles to username Ria......................................................................9
Create a SQL query with DROP system privilege to user Castor..........................................................9
Create a SQL query with EXECUTE object privilege to user Castor......................................................9
Write a SQL query to remove create, insert, update and execute privileges from user Castor...........9
Task-04: Create an application role with security in SQL Server Management Studio............................9
QUESTION2:...............................................................................................................................................11
Task-01: Discuss different types of exception handling techniques for identification of problems and
write down the appropriate actions required to take in order to resolve the problems.......................11
Task-02: As a SQL developer you are asked to create a customized exception handling for inserting a
value in a table......................................................................................................................................12
Q.1 Create custom error table ‘Error Table’......................................................................................12
Q.2 Write a procedure and execute with the exception handling using TRY…. CATCH when an error
occurs. Insert any duplicate value in the above table to find some error..........................................12
CONCLUSION:............................................................................................................................................14

INTRODUCTION
Microsoft SQL Server Management Studio gives the functionality of database accessibility. In
the given brief user has to make an unique authenticated user login id. Every authenticated
user has a specify role given as in the brief. In this practical we have created total seven
authenticated user in our Microsoft SQL Server Management Studio. Every user has a unique
system to define roles. After creating authenticated users we have to perform the specific task
for users. The query should be executed accordingly to our given brief and output should be
generated in the output window. The basic aim of this project is to create multiple
authenticated user in the SQL Server and all the user have all the permission to access the
database.

QUESTION 1: Complete the following tasks
Task-01: Create the users mentioned in above Table-1 for the database
using SQL server authentication with respective roles.
SQL Server authentication provides a system default process to create an authenticated user in
the MS SQL Server. Every user having a unique login id performs multiple tasks in MS SQL
Server.
First of all, we will create a new SQL Server account:
1. Open MS Server and Run
2. In an object, explorer open security tab and click on login and open a new login
Figure 1New Login
3. After clicking on New Login give Login Name and password to particular user & choose
default database name as your database where you want to create authenticated Login
Id and then click OK.

Figure 2Creating login id
4. After creating authentication Login ID you have to give particular Server Roles to users
to give some authenticated permission.

Figure 3assign server roles
5. After defining Server Roles we have to give User Mapping access to our user where we
have to select our database on which user was created and then we have to give
databases role permission to the user. By default database permission will be public.

Figure 4User Mapping
6. In the final step, we have to enable grant permission in the Status tab to the user.

Figure 5giving grant permission
Task-02: Explain the following fixed server/database roles that exist in
the SQL Server:
In SQL Server Management Studio there are several roles that can be defined to a user. These
server roles are the most important factor in SQL Server studio. We can give direct permission
to the user to access the database. Fixed server roles have a fixed set of permission.
 Sysadmin: The sysadmin fixed server roles specify all other roles and also has
unlimited scope for users. Sysadmin role members have all permission and
administrative privilege on all server and databases and server resources. The sysadmin
can put all SQL Server infrastructure under the control. It is important to know who else
has this fixed server role.
 Securityadmin: Member of this type of fixed server roles identify the security process
for a user to access the databases. Every process in security define an administrative
process for every user and gives the particular security key. To do so system generates

unique SSH key for every user to declare security. And if they have the permission of
database accessibility permission then the user can grant, deny, and revoke permission
to database server roles also. On another hand, they can reset the password of SQL
Server also. Securityadmin defines three basic type of roles:
1. Login Security
2. Database Security
3. Database Object
 DBcreator: Member of the dbcreator fixed server role can create, drop, alter and
restore any database. As the name defined this can create a database. The member of
this user is not something a normal user, is a user of administrative profile who can do
anything with the database. There is no need to define any server admin permission to
this user to access the database. The grant permission for this role is given below:
o Creation of database within SQL Server.
o Giving some access to junior DBA to access the database according to need.
o Some application will need to be specified to the authenticated user only for
some kind of security reason.
 DB-owner: The work of the database owner is to give some security accessibility
permission to the user who is a member of DB-owner. DB-owner has some privilege
permission that can restore the database within the server roles. DB-owner provides
database administration services to DBA.
 Db-accessadmin: Db-accessadmin provides only limited permission to the user. The
user only can create, edit, and update the database. The name Db-accessadmin already
specify that only accessibility permission given to the user of this fixed server role
members. Users of this fixed server role use an admin portal to access the permission
which is given in this server side role.
 Db-securityadmin: Db-securityadmin provides security to databases. The role of Db-
security admin is a most important role in the SQL Server fixed roles. Security is the
main issue in the databases. In this fixed server roles only that users can access who has
the accessibility of the Db-owner permission in the databases. To give security SQL
Server management studio provides authenticated user for the particular database so
that no one can make changes in the database.

Task-03: Explain the Policy-Based Management in SQL Server 2008 and
perform the following tasks:
Policy-based management in SQL Server uses to define particular system policy id that identifies
the system level process. Every database in this management has a unique policy id and have all
kind of permission to make some changes in the database. Policy-based services are system-
defined services that create automatically in the database. This management define the unique
identifier in the database when any random database created then the Policy-Id of the
database automatically generate.
Tasks:
Create a SQL query for user Alice and give ‘Select’ permission for your table.
GRANT SELECT ON OBJECT:: Auth_users to AliceS;
Create a SQL query for user Dara and give ‘Control’ permission for your
table.
GRANT CONTROL ON OBJECT:: Auth_Users to DaraD;
Create a SQL query for user Isabelle and give ‘Alter’ permission for your
table.
GRANT ALTER ON OBJECT:: Auth_Users to IsabelleO;
Assign one of the built-in server roles to username Ria.
To do so we have already give server admin permission to Ria which allows her to direct access
in the SQL Server Management Studio without any windows login. A login name id with RIaZ
already exist.
Create a SQL query with DROP system privilege to user Castor.
DROP USER CastorZ;
Create a SQL query with EXECUTE object privilege to user Castor.
GRANT EXECUTE TO CastorZ;
Write a SQL query to remove create, insert, update and execute privileges
from user Castor.
USE EMPLOYEE REVOKE CREATE CERTIFICATE FROM CastorZ;
REVOKE INSERT ON Auth_Users FROM CastorZ;
REVOKE UPDATE ON Auth_Users FROM CastorZ;
USE EMPLOYEE REVOKE EXECUTE FROM CastorZ;

Task-04: Create an application role with security in SQL Server
Management Studio.
An application role use to define the application to run its own server. Any application role who
has the capabilities to revoke server script and any database query to defines a most important
role in the SQL Server Management Studio. Steps of creating the application role are given
below:
To create an Application Roles you just need to choose your database and then in the security
tab, there is Role tab. Choose Role tab and there you will find a tab name Application Role, click
on that tab. After clicking a window popup comes where you just need to enter your application
role name and other data which are mandatory to specify.
Figure 6Application role

Figure 7Role Definition
QUESTION2:
Task-01: Discuss different types of exception handling techniques for
identification of problems and write down the appropriate actions
required to take in order to resolve the problems.
An error condition during an execution of the program is called Exception. The process which is
used to resolve the error in the program is called Exception Handling. For Exception Handling
process there are TRY and CATCH these two functions defined. These two functions are called a
block of code which is used to handle the exception.
TRY block is used to find the error in the code. If there is an error occurred in the code then the
exception which comes during the execution process will go into CATCH block and CATCH block
will catch the exception and give some error message to the user.
BEGIN TRY
/* Write commands here */
END TRY

BEGIN CATCH
/* Catch exception here */
END CATCH
Types of Exceptions:
There are two types of Exception:
 System Defined: In system defined Exception, Exceptions are generated by the system.
 User Defined: In user-defined Exception, Exception is generated by the user that
occurred in a TRY block and handled in the CATCH block.
Task-02: As a SQL developer you are asked to create a customized
exception handling for inserting a value in a table.
Q.1 Create custom error table ‘Error Table’.
select * from [dbo].[Error_Table]
Create table [dbo].[ErrorTable](
ErrorID uniqueidentifier Not null primary key constraint [ErrorTable_ErrorID]
default newsequentialid(),
AddedDate datetime not null constraint [ErrorTable_AddedDate] default
getutcdate(),
Message nvarchar(max) not null,
ObjectName varchar(100)
)
Q.2 Write a procedure and execute with the exception handling using TRY….
CATCH when an error occurs. Insert any duplicate value in the above table to
find some error.
ALTER Procedure [dbo].[Pro_inputerrors]
@ErrorID uniqueidentifier = Null Out
As
Set NoCount On;
Declare @Message nvarchar(4000) /*Holds error message*/;
Begin Try
/*Creates temp table to holds inserted errorID*/
Declare @Errors table(ErrorID uniqueidentifier) ;
Select @Message = ''Cannot insert duplicate UserName in object
''''dbo.Auth_User'''''';

/* Inserts error details*/
Insert Into dbo.ErrorTable (Message)
Output Inserted.ErrorID Into @Errors(ErrorID)
Select @Message;
/*Gets inserted errorID*/
Select @ErrorID = ErrorID From @Errors;
End Try
Begin Catch
/* Loads error detail*/
Raiserror(@Message, 16, 1);
End Catch;
Set NoCount Off ;
Return ;

CONCLUSION:
The Basic aim of this SQL Server project is to create multiple authenticated users in the
Management Studio. Every user has their unique login id in the management studio so that
every user having accessibility permission. The permission of the user is user-defined not
system defined, this term is needed to remember. All user having some system roles and also
have some permission to access functions. We have created and executed all the query as given
in the brief. If some permission is not working so it means that the user having some pre-
defined permission in the SQL Server Management Studio. All the task of this project is working
nicely.