2

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.

Pre-ticked boxes in Database Role Membership section

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.

Script button in New Login dialog box

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.

guest user dialog box. No role memberships.

3
  • I could not reproduce this in SQL 2019 CU15. I thought if you dropped the login and not the user, and try to add the same login this might happen. Was the database restored from a backup? Mar 17, 2022 at 15:32
  • I'm not sure I follow, where did your example script come from?...it does show it's adding the User to the db_datareader and db_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.
    – J.D.
    Mar 22, 2022 at 12:14
  • check if the guest user is a member of db_datareader and db_datawriter in that database
    – Bob Klimes
    Mar 22, 2022 at 16:00

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.