Drop multiple users from the same database in SQL Server 2000

You have the procedure named sp_dropuser

Loop through within a cursor and delete all the users with the following syntax

Exec SP_DropUser username

I am using sp_DropUser but i only have a basic understanding of SQL.

Could you show me how to construct the loop?

Thanks.

Try this syntax:

DECLARE @user varchar(100);
DECLARE usrcursor CURSOR FOR
SELECT users FROM table1;
OPEN usrcursor;
FETCH NEXT FROM usrcursor INTO @user;
WHILE @@FETCH_STATUS = 0
   BEGIN
           EXEC sp_dropuser @user
      FETCH NEXT FROM usrcursor;
   END;
CLOSE usrcursor;
DEALLOCATE usrcursor;
GO

make sure you can select all the users you can delete are available as part of a select query of from any table

Ok i guess you want me to import the list of users that need deleting into table1?

Either one of the methods below:

1. If possible as a query or as a comma separated value.
2. In a table.

If you have comma separated values, you can mix the procedure given in the link below and the one above to achieve your desired result

Oops.. Missed the Code:

    DECLARE @String    VARCHAR(100),
            @StringInput varhcar(1000);
    SET @StringInput = 'urcommaseparatedvalue';
    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))
 
        exec sp_dropuser @string
    END
    RETURN
END
GO

Select allOpen in new window

I prefer your first method and have already imported the table. However i am now getting an incorrect syntax error. can you help?

Can you provide me what you have exactly run and the issue which you have got out of it, So that I can help you..

Please see attached jpg.
Syntax-error.JPG

Thank you, it works great.