My understanding of SQL security is such that in order to have access to the server, you need a login. To access a database on that server, your login needs to be associated with a user in that database. Over time I have been consolidating security so that developers, QA, etc. are in various roles implemented as active directory groups. Those groups have logins on the server, so individual windows logins are not so prevalent on SQL instances. I grant proper accesses to the databases through these roles.
A common service I provide is to refresh non-production versions of databases from production. I'll do a backup and restore of a production database onto a development server, de-identify the data, rename it, etc.
I have noticed something recently in my environment that is troubling. I was granting access to a key table in production for a development team, when I noticed by using 'fn_my_permissions' that one of the developers has read and write access to the entire production database, rather than being restricted to read-only as I designed. I compared all of this developer's group memberships and found another group he was a member in that was assigned read/write - but only as a database user. There is no associated login for that group on that SQL server.
It is typical here to copy a 'baseline' production database and repurpose it elsewhere - whether as another production database, or a non-production version. I am starting to find other examples of this anomaly. To sum it up, the problem I see is that when a database is copied to another server, the users in that database that were previously linked to logins on the original SQL instance seem to retain the same level of access on the new SQL instance - even if there is no related login for them on new SQL instance! This strange effect seems limited to users representing windows group logins.
Here is a summary of what I am seeing: Server1 has a login tied to windows group A, which has been granted read/write to database X. Database X is then backed up and restored onto Server2, with logins but none related to group A. The database still shows a user for group A, and when I run 'fn_my_permissions' for a user in that group he has read/write to the database.
This particular server is running SQL Enterprise 2012 SP3 with the latest CU. I've seen the same thing on servers running 2008 R2, as well as a current build of SQL 2016.
Why is this happening? And what can I do to ensure that users do not inadvertently circumvent the controls I have put in place? It seems obvious I need to remove all of the unnecessary users after the databases are copied, but why?