When creating a new login via the GUI:
In the User Mapping section, certain databases will have some roles pre-ticked in the Database Role Membership section.
What controls this behavior? It's different for each database.
Oddly, this does not occur when creating a database user via the GUI.
Edit: Adding Script code generated after filling in username and selecting Map on ADCData DB.
USE [master] GO CREATE LOGIN [testLogin] WITH PASSWORD=N'', DEFAULT_DATABASE=[ADCData], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO use [ADCData]; GO CREATE USER [testLogin] FOR LOGIN [testLogin] GO USE [ADCData] GO ALTER ROLE [db_datareader] ADD MEMBER [testLogin] GO USE [ADCData] GO ALTER ROLE [db_datawriter] ADD MEMBER [testLogin] GO
Again, I'm not ticking these role membership boxes - they are pre-ticked once I select Map on the database.
Edit II: I'm not sure of the history of this DB - I'd estimate there's a 60% chance it's been restored from a backup. It also happens when adding any login - in the code example testLogin is not a login previously used.
Edit III: Above code was generated by clicking the Script button at the Login - New dialog. My issue is that the db_datareader and db_datawriter role options are ticked by default - I want to know what is determining that.
Also, I double-checked and there are no triggers at the server or db level.
Edit IV: My sincere appreciation to everyone working on this question. Unmodified Guest db user screenshot is below. No permissions are given, and CONNECT is revoked. I tried briefly granting CONNECT and seeing if that changed the UI, but it was still the same.