Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
add comment

2 Answers

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
add comment

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.

JR

share|improve this answer
add comment

Your Answer

 
discard

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.