Need a long term solution feasible to scale up my user base for access to RDS SQL Server.

To scale up your user base for access to RDS SQL Server while minimizing administrative overhead, you can leverage your existing Active Directory infrastructure and SQL Server roles:

1. Establish a trust relationship: If you have an on-premises Active Directory already set up with users defined, you can establish a one-way trust between your on-premises AD and your AWS Managed Microsoft AD. This will allow you to avoid duplicating user creation in the AWS Managed AD. You can find more information on setting up the trust relationship in the AWS Managed Microsoft AD.

2. Organize users into groups: Rather than managing individual users, organize your users into groups in your Active Directory based on their roles, such as Administrators, Developers, and Application Users. This will make it easier to manage permissions and access control on a group level.

3. Leverage SQL Server roles: Use roles within SQL Server to manage permissions and access for the groups you created in step 2. By assigning the appropriate roles to each group, you can grant access to resources at the server or database level, as well as apply fine-grained access control (both deny and allow).

• Server-level roles: You can use predefined server-level roles, like sysadmin, serveradmin, or securityadmin, or create custom server-level roles to fit your organization's needs. Grant these roles to the corresponding AD groups. For more information, refer to the SQL Server documentation on server-level roles.

• Database-level roles: Similarly, you can use predefined database-level roles, like db_owner, db_datareader, or db_datawriter, or create custom database-level roles. Grant these roles to the corresponding AD groups. For more information, refer to the SQL Server documentation on database-level roles.