SQL Queries for Creating a rollback point and to rollback to that specific point

Question

Hi,

As per my project requirement i want to perform two operation

1. Password Change
2. Unlock Account(Only unlocking account, no password change!)

I want return success only if both the transactions succeeds. Say if password change succeeds and unlock fails i cannot send success or failure. So i want to create a rollback point before password change, if both queries executes successfully i will commit the transaction. If one of the query fails i will discard the changes by rolling back to the rollback point. I am doing this in C++ using ADO. Is there any SQL Queries,using i can create the rollback point and reverting to rollback point and commiting the transaction

I am using below commands for Password change

ALTER LOGIN [username] WITH PASSWORD = N'password'

for Unlock account

ALTER LOGIN [%s] WITH CHECK_POLICY = OFF
ALTER LOGIN [%s] WITH CHECK_POLICY = ON

Thanks in advance!!

Santhosh

Answer 1

some SQL supports transactions. Put the queries within a transaction-block. If any query fails within the block, all is rolled back. This is how it looks for MySQL:

BEGIN;
ALTER this CHANGE that;
...
COMMIT;

Answer 2

The nature of DDL statements is that each one constitiutes its own discrete transaction. Think of it this way: a COMMIT is issued at the start of the DDL statement and another at the end of a successful completion of the statement. There is no option to rollback a successful DDL statement. Find out more.

However the ALTER LOGIN syntax would allow you to combine the password change and unlocking statements into one:

ALTER LOGIN [username] WITH PASSWORD = 'password' UNLOCK ;

There is no single statement to unlock a password without changing the password, so for that option you need to toggle the CHECK_OPTION in the manner you describe.