What happens when permission is revoked in middle of work?

This is one of the question asked by my friend and it seems very simple. I had a discussion with him and said that whatever the transaction it has started it will succeeded, however what I told him is not correct. I told him I’ll test it out and the answer which I gave it to him is partially correct. If the transaction gets completed with in begin tran (before commit or rollback) you can close the transaction even if the permission is revoked however if the transaction is not completed within begin tran then it will fail stating that the user don’t have access.

I’m just posting the scripts which I used for testing. I would love to hear your feedback so that we have an healthy argument on this topic. You need to open two connections one with your admin ID and other one with the user which you are going to create. In this article if I say connection one then it’s admin access and connection two is the new login

Use the script below to initialize the data

--Connection One
CREATE DATABASE Permissiontest
GO
CREATE LOGIN LoginA WITH PASSWORD='Test123', DEFAULT_DATABASE=[Permissiontest],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE Permissiontest
GO
CREATE USER LoginA FROM LOGIN LoginA
GO
EXEC sp_addrolemember 'db_datareader','LoginA'
EXEC sp_addrolemember 'db_datawriter','LoginA'
GO
CREATE Table Test (id INT, name VARCHAR(10))
INSERT Test VALUES (1,'Sagar')
GO

Test 1 (Single Transaction)

Now create another connection with the new login. User name is “LoginA” and password is “Test123”, run the below query in connection two

--Connection Two
BEGIN TRAN
UPDATE Test
SET name = 'Govind'

Now switch back to Connection one and execute the below command to revoke “LoginA” access to db

--Connection One
USE [Permissiontest]
GO
DROP USER [LoginA]
GO

Now switch back to Connection two and execute the below command to commit or rollback the transaction

--Connection Two
COMMIT TRAN
GO
SELECT name FROM Test

You can see that even after revoking the permission commit or rollback is successful. Now lets do the same test in a batch processing lets see what happens there

TEST2 (Batch Transaction)

Now switch to Connection one and execute the below to grant access to “LoginA”

--Connection One
USE Permissiontest
GO
CREATE USER LoginA FROM LOGIN LoginA
GO
EXEC sp_addrolemember 'db_datareader','LoginA'
EXEC sp_addrolemember 'db_datawriter','LoginA'

Now switch to Connection two and execute the permission

--Connection Two
UPDATE Test
SET name = 'Govind'
WAITFOR DELAY '00:00:03'
GO 15

Let the script executes in connection two in the mean time go to connection one and execute the command below to revoke the permission

--Connection One
USE [Permissiontest]
GO
DROP USER [LoginA]
GO

You can see the results that it will complete till the rows processed and then rest of the rows it failed to update stating it doesn’t have access to that table as shown below

Msg 916, Level 14, State 1, Line 1
The server principal “LoginA” is not able to access the database “Permissiontest” under the current security context.
** An error was encountered during execution of batch. Continuing.

Once you are done with testing use the script below to drop the database , run it in connection one context

--Connection One
ALTER DATABASE Permissiontest SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE Permissiontest
GO
DROP LOGIN LoginA