Script to delete a login and associated users from SQL Server

Occasionally, you might need to drop a single login from a SQL Server, however if that login has users in a lot of databases, it may be tedious to delete the associated user account from every database. The script shown below performs the necessary action for you. As-is, the script is configured to run in “debug” mode, where it will simply print out the dynamically-created T-SQL script that would be executed if you’d set the @DebugOnly parameter to 0. You should modify the @ServerPrincipalName to reflect the name of the login you want to delete, and the @DatabasePrincipalName to the name of the associated user that should be removed from any databases where it is present. Typically, both parameters will be set to the same value, however in some cases the login and user are created with slightly different names; perhaps the login is an Active Directory account with the domain name, whereas the user does not include the domain name.

Poor Faceless Suit!

The script dynamically constructs the required DROP LOGIN and DROP USER T-SQL statements such that all users matching @DatabasePrincipalName are dropped, then the login matching @ServerPrincipalName is dropped.

Let me know if you have any issues with the script, or if you’d like to make a suggestion for an improvement.

This post is part of our series on SQL Server Security.