Execute a script/stored prodedure when specific Error_Number is encountered

Question

I've created a stored procedure (proc_dropUser) to loop through and drop a user from all databases.

But many times I encounter error 15138 when a user owns a schema.

Hence I created another procedure (proc_altAuthorization) to alter the authorization of the schemas owned by the database user.

What I want to do is, whenever I encounter the specific error while executing proc_dropUser, then execute proc_altAuthorization, and then return to the first procedure and continue.

Something like:

CREATE proc_dropUser
    @userName

    --Cursor to DROP USER from each @dbName
   IF ERROR_NUMBER() = 15138
       EXEC proc_altAuthorization @userName, @dbName

   FETCH NEXT FROM cursor INTO @user
GO

And proc_altAuthorization will accept the parameters and alter the authorization.

CREATE proc_altAuthorization
    @userName, @dbName

--build dynamic sql query
SET @dsql = N'USE ' + @dbName + N'
ALTER AUTHORIZATION ON SCHEMA:: '+@schema + ' TO dbo'
EXEC(@dsql)
RETURN --not sure!

I was thinking TRY CATCH block could be used, but not sure how.

Answer 1

It's been a while that I found a solution to my problem, but realised I never posted it here. So here's the solution that I found. It implements the same logic of TRY-CATCH which I had mentioned in my question, but did not have the technical knowledge of how to implement.

CREATE PROCEDURE proc_dropUser
    @userName NVARCHAR(128)
AS
BEGIN
    DECLARE @dbName NVARCHAR(128)
    DECLARE @sql NVARCHAR(MAX)
    DECLARE @schema NVARCHAR(128)
    
    -- Declare a cursor for iterating through all databases
    DECLARE db_cursor CURSOR FOR
    SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
    
    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @dbName
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            -- Build the dynamic SQL to drop the user from each database
            SET @sql = N'USE ' + QUOTENAME(@dbName) + N';
                        IF EXISTS (SELECT * FROM sys.database_principals WHERE name = @userName)
                        BEGIN
                            DROP USER ' + QUOTENAME(@userName) + N';
                        END'
            EXEC proc_executesql @sql, N'@userName NVARCHAR(128)', @userName
            
        END TRY
        BEGIN CATCH
            -- Check if the error is 15138 (user owns a schema)
            IF ERROR_NUMBER() = 15138
            BEGIN
                -- Execute proc_altAuthorization to alter the schema ownership
                EXEC proc_altAuthorization @userName, @dbName
                -- Retry dropping the user after the schema ownership is changed
                SET @sql = N'USE ' + QUOTENAME(@dbName) + N';
                            DROP USER ' + QUOTENAME(@userName) + N';'
                EXEC proc_executesql @sql
            END
        END CATCH
        
        -- Fetch the next database
        FETCH NEXT FROM db_cursor INTO @dbName
    END
    
    CLOSE db_cursor
    DEALLOCATE db_cursor
END

Hope this helps others who come across a similar need. Cheers!