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).
- In this case, use
ALTER AUTHORIZATION
to change database ownership to another 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;