Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In my demoenvironment I juggle lots of SQL databases. Each database has lots of Users, which are linked to separate Security->Logins on the server.

Over time I keep ending up with many (like hundreds) of orphans Security->Logins that are not linked to any users in any of the databases.

Is there any SQL statements I can run to drop the unused Logins - i.e. the logins that have no mappings to database users?

share|improve this question

i would suggest building a select statement that ties the individual database sysusers table to the master..sysxlogins.

some of the system procs you want to explore are sp_MShasdbaccess and sp_validatelogins.

share|improve this answer

Use sp_droplogin (or Management Studio)

Is there a subtext to your question i.e. how to you drop only logins that aren't linked to a database user? If so I think (though I'd try it to be sure) that sp_droplogin will only delete logins that are not mapped to a db user and will fail if they are mapped.


share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.