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.
db_datareader
anddb_datawriter
roles. If you're saying this is the script from the GUI when you click on the "User Mapping" section, how did you obtain it?...the Profiler? Outside of that, you should verify there's no Server or Database triggers that may be causing this behavior.