Dropping Users from SQL Server Database via Powershell

Question

I am trying to drop users from a SQL server database via PowerShell using the following code:

$sql_server = "mysqlserver"
$dbname = "mydb"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server")($sql_server)
$db = $server.Databases["$dbname"]
$logins = $db.users

if ($logins.count -gt 0) {
    foreach ($login in $logins) {
        if ($login -notmatch "\[dbo\]|\[guest\]|\[INFORMATION_SCHEMA\]|\[sys\]") {
            $user = $login -replace '[^a-zA-Z\.\\]'
            write-host "Dropping $user"
            $db.Users[$user].Drop();
        }
    }
}

However I am getting an error stating:

Collection was modified; enumeration operation may not execute. + foreach ($login in $logins) { + ~~~~~~ + CategoryInfo : OperationStopped: (:) [], InvalidOperationException + FullyQualifiedErrorId : System.InvalidOperationException

I think its saying that $logins is being changed because I'm deleting users? I'm not sure how to work around that though?

EDIT

The following should work. Thanks to @Neil Hibbert for the help.

$sql_server = "mysqlserver"
$dbname = "mydb"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server")($sql_server)
$db = $server.Databases["$dbname"]
$logins = $db.users

if ($logins.count -gt 0) {
    for ($i=0; $i -lt $logins.count; $i++) {
        if ($logins[$i] -notmatch "\[dbo\]|\[guest\]|\[INFORMATION_SCHEMA\]|\[sys\]") {
            $user = $logins[$i] -replace '[^a-zA-Z\.\\]'
            write-host "Dropping $user"
            $db.Users[$user].Drop()
        }
    }
}

I think this is more of a .NET thing. Whilst iterating over collections with a foreach-loop, individual elements may not be modified, which is what you are trying to do. If you replaced the foreach loop with a for-loop and tried the same action, it should work out successful. — Neil Hibbert
– Neil Hibbert, Commented Jun 15, 2017 at 12:49
Not sure how I can change this to a for loop? I tried for ($i=0; $i -lt $logins.count; $i++) { if ($login[$i] -notmatch "[dbo]|[guest]|[INFORMATION_SCHEMA]|[sys]") { $user = $login[$i] -replace '[^a-zA-Z\.\]' write-host "Dropping $user" $logins[$i].Drop(); } } but that didn't work. — Brad
– Brad, Commented Jun 15, 2017 at 23:27
Just to clarify, your comment code is slightly different than your code snippet in that your comment code reads '$logins[$i].Drop()' rather than '$db.Users[$user].Drop()' is this just a typo or the code you ran in your script? — Neil Hibbert
– Neil Hibbert, Commented Jun 16, 2017 at 11:00
Switching it to $db.Users[$user].Drop() results in "Cannot index into a null array on if ($login[$i] -notmatch "[dbo]|[guest]|[INFORMATION_SCHEMA]|[sys]")" — Brad
– Brad, Commented Jun 16, 2017 at 11:29

Neil Hibbert · Accepted Answer · 2017-06-16 13:14:26Z

2

I'm pretty certain the error message you are/were seeing is because you had set $logins to the list of $db.users and then tried to delete a user during the foreach loop.

Initializing $logins with $server.Logins (rather than $db.users) and then checking $db.Users.Contains($login) before calling $db.Users[$login].Drop() in your loop will fix your issue...

answered Jun 16, 2017 at 13:14

Neil Hibbert

8625 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dropping Users from SQL Server Database via Powershell

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related