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


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