• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1009
  • Last Modified:

Drop multiple users from the same database in SQL Server 2000

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
0
SigmundFraud
Asked:
SigmundFraud
  • 6
  • 5
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
0
 
SigmundFraudAuthor 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.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
SigmundFraudAuthor Commented:
Ok i guess you want me to import the list of users that need deleting into table1?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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

0
 
SigmundFraudAuthor Commented:
I prefer your first method and have already imported the table. However i am now getting an incorrect syntax error. can you help?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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..
0
 
SigmundFraudAuthor Commented:
Please see attached jpg.
Syntax-error.JPG
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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

0
 
SigmundFraudAuthor Commented:
Thank you, it works great.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now