Can Certain SQL User Roles Avoid Blocking?

Posted by

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

Long Story Short

We recently came across a case where blocking was being observed when performing a DDL operation, while querying system views concurrently. However, performing the same query with a different user was not triggering the same blocking pattern. In this article, we will reproduce the same steps and briefly go through the reason why specific user roles may avoid blocking in certain scenarios. 

 

Setting Up the Stage

To reproduce this scenario, we just need to create a table and two users: one will be part of the db_datawriter role with ALTER permission on the respective table, and the other have the db_owner role assigned. We begin by creating the respective logins on the master database:

 

 

CREATE LOGIN writer WITH password='112357!Abc!'; CREATE LOGIN [owner] WITH password='112357!Abc!';

 

 

Then we create the table, the users and assign the respective roles / permissions:

 

 

/* Create the table */ CREATE TABLE MyTable(columnA int NOT NULL); /* Create users */ CREATE USER writer FROM LOGIN writer; CREATE USER [owner] FROM LOGIN [owner]; GO /* Assign Roles */ EXEC sp_addrolemember N'db_datawriter', N'writer'; GRANT ALTER ON MyTable TO writer; EXEC sp_addrolemember N'db_owner', N'owner'; GO

 

 

Block Me

We will be starting by truncating the table we have previously created, leaving the transaction deliberately opened, to facilitate reproducing the behavior. Keep in mind that, the execution of this query can be made by either of the users we have created.  

 

 

BEGIN TRANSACTION TRUNCATE TABLE MyTable

 

 

With the same user, we will open a new session and query information from tables, schemas and objects system views:

 

 

SELECT t.[TABLE_CATALOG], t.[TABLE_SCHEMA], t.[TABLE_NAME], t.[TABLE_TYPE], tv.[CREATE_DATE], tv.[MODIFY_DATE] FROM [INFORMATION_SCHEMA].tables t JOIN sys.schemas s on s.name = t.[TABLE_SCHEMA] JOIN sys.objects tv on tv.name = t.[TABLE_NAME] and tv.schema_id = s.schema_id and tv.parent_object_id = 0

 

 

When we execute the second query with the writer user, we verify that it gets blocked:

 

Francisco_do_O_0-1668849124298.png

 

We can use the query below to obtain more information about this blocking event:

 

 

WITH cteBL (session_id, blocking_these) AS (SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s CROSS APPLY (SELECT isnull(convert(varchar(6), er.session_id),'') + ', ' FROM sys.dm_exec_requests as er WHERE er.blocking_session_id = isnull(s.session_id ,0) AND er.blocking_session_id <> 0 FOR XML PATH('') ) AS x (blocking_these) ) SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these , batch_text = t.text, input_buffer = ib.event_info, host_name, login_name,wait_type,wait_time,wait_resource FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id INNER JOIN cteBL as bl on s.session_id = bl.session_id OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib WHERE blocking_these is not null or r.blocking_session_id > 0 ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;

 

 

And we can confirm that the operation gets blocked when trying to acquire a schema stability lock. The query accesses the metadata, but the truncate operation is holding the lock for the system table, so the second query is not able to access the respective information.

 

Francisco_do_O_1-1668599485067.png

 

Unstoppable

However, if we execute the latter query using the owner user, we will confirm that the second query will not get blocked, and results will be immediately presented:

 

Francisco_do_O_1-1668850032595.png

 

Conclusion

So, why did blocking occur in the first case? Basically, the truncate query causes the schema of the respective table to be locked while all extents are deallocated. Meanwhile, the second query is executed and will have to wait while the lock from the first query is held, so then SQL can check if the user has the necessary permissions to perform the operation.

 

The only question left is, why the same query was not blocked when executed by the db_owner user? The simple reason is that, for certain user roles, permission checks can be bypassed. This behavior is by design and is documented here - point number one of “General steps of the algorithm”.  

 

To minimize the occurrences of such blocking events, and since assigning roles with higher permissions may not be appropriate due to security reasons, it should be considered the possibility of separating both operations to avoid that they execute concurrently.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.