Chapter 8
Commit, Rollback
and User
login
Lesson Objectives
• By the end of this lesson, students should be able to:
– Explain the purpose of COMMIT and ROLLBACK commands in SQL.
– Differentiate between COMMIT and ROLLBACK.
– Use transaction commands to maintain data integrity.
– Create logins and users in SQL Server.
– Assign roles and permissions to database users.
COMMIT and ROLLBACK
• COMMIT and ROLLBACK are two crucial Transaction Control
Language (TCL) commands that help maintain data
integrity and consistency.
• While COMMIT ensures that all changes in a transaction are
permanently saved.
• ROLLBACK provides a mechanism to undo changes when
something goes wrong.
Understanding the differences
• Understanding the differences between these commands is critical
for database administrators, developers, and anyone working
with transactional data to ensure reliable and error-free
database operations.
• Proper usage of these commands ensures reliable and error-free
database operations while maintaining data stability.
Difference Between COMMIT and ROLLBACK
• The table below highlights the key differences between COMMIT
and ROLLBACK in SQL:
Feature COMMIT ROLLBACK
Undoes changes made by the current
Function Permanently saves changes made by the current transaction.
transaction.
Reverts the database to its previous state
Undo Capability Cannot undo changes after execution.
before the transaction.
Used when the transaction fails, is
When Applied Used when the transaction is successfully completed.
incorrect, or aborted.
Ensures that errors do not affect the
Data Integrity Ensures that changes are saved permanently.
database by undoing partial changes.
Syntax COMMIT; ROLLBACK;
No changes are rolled back even if errors occur after the Automatically undoes uncommitted
Error Handling
COMMIT statement. changes in case of errors or failures.
ROLLBACK
• The ROLLBACK command is used to undo all changes made in the current transaction.
• If an error occurs, you can roll back the data to its previous state.
• Syntax:
ROLLBACK TRANSACTION;
• Example
BEGIN TRANSACTION;
UPDATE Employees
SET Salary = Salary + 500
WHERE Department = 'Sales';
-- Oops! We realize it's a mistake
ROLLBACK TRANSACTION;
Explanation:
• ROLLBACK cancels the UPDATE — no data is
changed in the table.
COMMIT
• The COMMIT command is used to save all the changes
made in the current transaction permanently to the
database.
• Syntax:
COMMIT TRANSACTION;
Example and Explanation
• Example
BEGIN TRANSACTION;
UPDATE Employees
SET Salary = Salary + 500
WHERE Department = 'Sales';
COMMIT TRANSACTION;
• Explanation:
– BEGIN TRANSACTION starts the transaction.
– The UPDATE statement increases salaries.
– COMMIT permanently saves the changes — now they cannot be undone.
Creating Login and user
• Creating a login in SQL Server is a crucial step in securing your
database and controlling access to it.
• A login is an account that allows a user to connect to the SQL Server
instance and access the database.
• Creating a user login in SQL Server involves two steps:
1. Create a login at the server level (this gives access to the SQL
Server instance).
2. Create a user at the database level (this gives access to a specific
database).
Step 1: Create a Login
• This is done at the server level using the CREATE LOGIN statement.
• Syntax:
CREATE LOGIN login_name
WITH PASSWORD = 'strong_password';
• Example:
CREATE LOGIN ali_login
WITH PASSWORD = 'Ali@12345';
Step 2: Create a Database User
• Once you have a login, you must map it to a user in a specific
database.
• Syntax:
USE database_name;
CREATE USER user_name FOR LOGIN login_name;
• Example:
USE SupermarketDB;
CREATE USER ali_user FOR LOGIN ali_login;
Step 3 (Optional): Grant Permissions or Roles
• After creating the user, you must give them the necessary
permissions.
• After creating the user, you must give them the necessary
permissions.
• Example – assign built-in database roles:
ALTER ROLE db_datareader ADD MEMBER ali_user;
ALTER ROLE db_datawriter ADD MEMBER ali_user;
Summary
Step Command Description
CREATE LOGIN ali_login WITH PASSWORD =
1 Create server login
'Ali@12345';
USE SupermarketDB; CREATE USER ali_user
2 Create user in a database
FOR LOGIN ali_login;
ALTER ROLE db_datareader ADD MEMBER
3 Give permissions
ali_user;