DROP LOGIN (TSQL Statement)

drops the specified login.

Syntax

DROP LOGIN login_name

Remarks

Only sa is allowed to execute this statement.

It is not allowed to drop a login which has mapped users.

It is not allowed to drop a login which is owner of a database (the user dbo is mapped to this login).

To display all users and their logins, use the command below:

SHOW ALL U

To filter this list on your terminal, you can pipe the output into the grep utility to print only the lines containing the name of the login.

You can easily create a script to delete all the users mapped to a login with the command below:

-- replace the login name "vladimir" by the login name of your choice

SHOW ALL TEMPLATE = '{{if eq .login_name "vladimir"}}USE {{.database_name}}; DROP USER {{.user_name}};{{end}}' USERS

-- this command is the same as above, but displays a warning if the login to drop is owner of a database.

SHOW ALL TEMPLATE = '{{if eq .login_name "vladimir"}}USE {{.database_name}}; {{if eq .user_name "dbo"}}-- DROP USER {{.user_name}};  IMPOSSIBLE: login {{.login_name}} is owner of database {{.database_name}} !{{else}}DROP USER {{.user_name}};{{end}}{{end}}' USERS

This command will print lines like:

USE mydb; DROP USER bob;
...

Examples

DROP LOGIN vladimir;
RSQL, a simple alternative to Microsoft SQL Server