I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.
One of the common issues that I would run into with refreshing development and test environments are the orphaned users. These are accounts that typically exist in production, but not in development. The logins and users are different in these environments, and often there isn’t a login on the development instance. This creates an orphaned user: one that exists in the database, but has no instance level mapping.
Cleaning up these users isn’t that hard, often with a removal of the user, mapping to a different login, or repairing this user by adding back the server login. These aren’t difficult tasks, but the logic to write a script to quickly fix this, especially in a DR situation, may not be handy.
dbatools can help here with a few cmdlets. I’m not recommending this is any better than the T-SQL, but if you keep dbatools on your instances, this is a quick way to fix things.
With Get-DbaOrphanUser, we can quickly get a list of those users that aren’t matched on the restored server. I can limit this to an instance or a database, but it gives me a list of users that I can then pipe into one of a few other cmdlets: Repair-DbaOrphanUser and Remove-DbaOrphanUser. These two cmdlets will do, as the British say, what it says on the tin. They’ll remove or remap the users, which can make it easy to quickly get your users working again.
Note that you will want to ensure this does what you expect and run with the -WhatIf command to be sure that you aren’t altering users that you don’t want to change.
This isn’t necessarily the way I’d always clean up users, but as part of a flow that might include automated restores, data masking, and other steps, being able to access orphaned user data and repair users from PoSh is something I’d certainly consider.
Voice of the DBA 