get-sqluser Issue

Aug 18, 2010 at 7:08 PM
Edited Aug 18, 2010 at 7:19 PM

Hey, love SQLPSX from what I've tried so far and I'm attempting to use it for some automation purposes. My problem is, after restoring a database, I need to remove all the users on it but when I pull all the users for a database using the get-sqluser command, it doesn't return all of the users that appear in SQL Studio. I'm assuming this has something to do with the fact that it's a database restore using a backup from a different database. Any ideas on why it would be doing this is greatly appreciated. Thanks! A good workaround would just be a way to completely remove all users on the restored DB.

Aug 19, 2010 at 3:25 AM

When building the list of users, orphaned users are eliminated from the output in SQLPSX. This was done in order to show only logins/users which truly have access. I guess I was more interested in creating a report than taking action on orphaned users. As you are aware restoring a database to another will cause mismatched sids for SQL Server accounts (orphaned users). On a restore you'll want to either use the sp_change_users_login procedure to fix the orphaned users or you could drop the users.

In a future release I'll look at adding a -force switch to show orphaned users. It shouldn't be too difficult to implement (maybe change a couple of lines which check if the user has access).


Aug 20, 2010 at 6:37 PM
Edited Aug 20, 2010 at 8:25 PM

I'd like to be able to just drop all users from the restored DB without having to enumerate the users. I'm not sure how to go about that using SQLPSX though. I'm a system engineer, not a SQL guru LOL

Aug 20, 2010 at 9:09 PM

You do something like this:

$db = get-sqldatabase "Z002\sql2k8" pubs 
$db.Users | where {$_.Name -eq $false} | foreach {Remove-SqlUser -dbname $db -name $}
If the user owns objects or schema attempting to the drop the user may generate errors.