dbatools is a free PowerShell module with over 200 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Remove-SqlOrphanUser

Drop orphan database users with no matching SQL Server login. If a matching login exists, the drop will not be performed unless you specify the -Force parameter.

Screenshots

RemoveOrphanUser_All_From_DB5
Remove all orphan users with no matching login from database ‘db5’
RemoveOrphanUser_All_From_DB7_WithMatchingLogin
Try to remove orphan users that have matching login
RemoveOrphanUser_All_From_DB7_With-Force
Remove orphan users even the ones that have a matching login. Using -Force

Examples

To find and drop all orphan users without matching login of all databases present on server ‘sql2005’

Remove-SqlOrphanUser -SqlServer sql2005

To remove from all databases the user ‘OrphanUser’ only if not have their matching login

Remove-SqlOrphanUser -SqlServer sqlserver2014a -Users OrphanUser

To find and drop all orphan users even if exists their matching login on both db1 and db2 databases

Remove-SqlOrphanUser -SqlServer sqlserver2014a -Databases db1, db2 -Force

To remove from all databases the user OrphanUser EVEN if exists their matching login.

Remove-SqlOrphanUser -SqlServer sqlserver2014a -Users OrphanUser -Force

This command was created by Cláudio Silva. You can find Cláudio on Twitter and LinkedIn.

Get-Help

From PowerShell, execute Get-Help Remove-SqlOrphanUser -Detailed for more information on this function.

Source Code

Want to see the source code? View Remove-SqlOrphanUser.ps1 on GitHub
 

Related commands