Hi, I came across something that changed the way I see SQL Server permissions.
Way back, we created a LOGIN in SQL Server for an AD group. At the time, we gave the new LOGIN db_datareader permission to a database. As expected, all of the people in the AD group could SELECT from that database.
Later, we dropped the LOGIN but did not drop the database USER that was associated with that LOGIN. We thought that because the database USER was mapped to a LOGIN that no longer existed, it would be a dead end for access.
Some time later, we added a new AD group as a LOGIN and gave it permission to other databases. There is a person in the new AD group who surprisingly gained db_datareader permission on the first database because he is a member of the AD group that is still a USER of the first database (with no matching login) even thought the new LOGIN does not grant access to that first database.
It was a surprise and tells me that when we drop a login, we really do need to clean up the associated database users. It states as much in SSMS when dropping a login but I did not understand the correlation between database users and instance logins that don't have explicit permission to a database.
From Management Studio: "Deleting server logins does not delete the database users associated with the logins. To complete the process, delete the users in each database." <--- this seems important.