patternsqlMinor
DROP USER takes too long when there are many users
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
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
Remember, I have to run it over 160 times for the instance I'm writing about.
This is the SQL I'm using:
The problem is not coming from cursor; it's the actual
Using
Don't pay attention to the duration, the
Profiler shows over 125,000 reads to execute
Service Broker is not enabled.
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_curThe 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.
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.
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.