I have an SQL Server 2008R2 that had lots of databases.
The procedure was that a logins were created and users were mapped to databases.
Most of these databases have been removed from the server now but it ended up having LOTS of logins that are associated to users mapped to non existing databases.
Is there a batch way to get rid of the these logins and their assosiated users?
Thank you
Best Answer
First of all, Orphaned Users is a term to define database users associated to non-existing logins. According to your explanation, that is not the case for you and your logins.
If databases have been removed from the server, there will be no orphaned user since they will be gone with the database, there will be logins which have no associated users and probably not in use.
To find these logins with no database mapping please look here!
However, To get rid of these logins with no mapping you need to be sure that there is no activity, you can try several methods such as Extended Events, Profiler, Server Audit etc to capture all login activity, parse login events and detect unused logins. Extended events would be the best one since it is faster and requires less resource. I have encountered the same problem and developed a solution for this years ago with Extended Events. The script creates an SQL Agent job which creates everything necessary dynamically.An extended events to capture all login events on server writes events to a file on C drive which is then parsed by the job and logs everything into a summary table. I can upload the script if you want but unfortunately there is no documentation or blog post about it (yet).