HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

DROP USER takes too long when there are many users

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
areuseruserslongtoodropmanywhentheretakes

Problem

On a SQL Server 2014 instance with enough RAM and fast disks, there are over 160 users that have access to a database. For some reason unbeknownst to me, running the command DROP USER [username] in this database takes up to 5 seconds per user.

Remapping users to logins and restoring their permissions is very fast.

Within the context of refreshing DEV databases from production, I have to drop and recreate all database users. So yes dropping the database users and recreating them is necessary.
How do I speed up the DROP USER command?

Remember, I have to run it over 160 times for the instance I'm writing about.

This is the SQL I'm using:

DECLARE drop_user_cur CURSOR FOR 
SELECT name FROM #drop_users

OPEN drop_user_cur
FETCH NEXT FROM drop_user_cur INTO @user

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'use [' + @db_name + '] DROP USER [' + @user + ']'
    BEGIN TRY
        print @sql
        EXECUTE(@sql)
    END TRY
    BEGIN CATCH
        print 'ERREUR : ' + @sql
    END CATCH
    FETCH NEXT FROM drop_user_cur INTO @user
END

CLOSE drop_user_cur
DEALLOCATE drop_user_cur


The problem is not coming from cursor; it's the actual DROP USER that's taking up to 5 seconds.

Using sp_whoisactive, the wait_type is NULL.

Don't pay attention to the duration, the DROP and CREATE USER were being run in a WHILE loop which is why it's saying more than a minute.

Profiler shows over 125,000 reads to execute DROP USER.

Service Broker is not enabled.

Solution

The resolution to this issue was enabling service broker on the database as so.

ALTER DATABASE [Database_name] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;


After enabling service broker for the database, drop users were practically instantaneous.

Kin asked if the service broker was enabled in a previous comment which sent me searching in the right direction.

Code Snippets

ALTER DATABASE [Database_name] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

Context

StackExchange Database Administrators Q#140092, answer score: 6

Revisions (0)

No revisions yet.