Delete/remove orphaned users and their schema from server/database

Question

I'm new to the role of being a SQL Server DBA for SQL Server 2008 R2.

I need help on how to find out orphaned users in SQL Server and how to delete/remove the orphaned users and their corresponding schema from the database.

Please provide a step by step solution.

score 0 · Answer 1 · edited Nov 08 '18 at 14:50

To find out the orphan users of a database say "ABC", you need to query below on that database:i.e
```
USE ABC

sp_change_users_login @Action='Report'
```

This will list out all the orphan database users for that database. In order to resolve the orphan users, use sp_change_users_login, with query below

USE ABC

sp_change_users_login
    @Action='update_one',   
    @UserNamePattern='database_user_name_comes_here', 
    @LoginName='login_name_comes_here'

Also, suggested reads: How To Avoid Orphaned Database Users with SQL Server Authentication and Fixing Orphan users

Delete/remove orphaned users and their schema from server/database

1 Answers1