Dropping SQL Server logins without dropping associated the database users creates orphaned database users. By default, the DROP LOGIN statement in SQL Server only removes logins, but not database(s) users that are linked to these logins. The best practice is to delete all databases users that are mapped to this login first and then delete the login itself. When logins are deleted, and users remain orphaned, DBAs have a cleanup task to perform from time to time. This is to make sure that an orphaned user is not misused (to prevent a potential security issue). How can we make sure that we don’t forget to delete database users before dropping logins?

Please read the latest MSSQLTips post: “DDL Trigger Example in SQL Server for Login Deletion“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.