We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Drop multiple users from the same database in SQL Server 2000

Medium Priority
1,482 Views
Last Modified: 2012-05-06
Hello

I have multiple user names that need to be dropped from the same database. Can anyone spare me the tedious task of dropping over 500 of these one by one?

Thanks
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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

Author

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

Could you show me how to construct the loop?

Thanks.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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

Author

Commented:
Ok i guess you want me to import the list of users that need deleting into table1?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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

Open in new window

Author

Commented:
I prefer your first method and have already imported the table. However i am now getting an incorrect syntax error. can you help?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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..

Author

Commented:
Please see attached jpg.
Syntax-error.JPG
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
It is working fine for me without any issues.
Give it a try again.
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

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thank you, it works great.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.