Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In SQL Server 2005, is there a way to find users that either don't exist at the server level (an account that was deleted at server level but wasn't disassociated from databases before it was deleted) or accounts that aren't linked (an account may have been deleted at the server level but not db level, then readded but the db level was never cleaned up).

I've got a very messy server and it would be awesome if there was a query to run to find these.

2 Answers
2

Apologies to the creator of this script for not giving credit but I've no idea where I sourced it from. It's been in the toolbox for a while.

This iterates through all databases and lists the orphaned users by database, along with the drop command to remove them. There may be a neater/newer way of handling this but this appears to function correctly on 2005-2012.