The underlying issue is that there is a difference between a SQL Server Login and a Database User.
A SQL Server Login can be either a native SQL Server Login or a Windows Authenticated User / Group.
A SQL Server Login authenticates against the SQL Server. After the login the SQL Server looks if the SQL Server Login is associated with a Database User in the database it is trying to access.
The Database User has the privileges to modify or view data in the database. If a link exists, then the SQL Server Login is able to modify data in the database vie the permissions assigned to the Database User.
When you restore a database from one server to another then you are transporting the Database Users within the backup. However, the SQL Server Logins are not transported with the database backup.
You have various options
Option 1: Create a SQL Server Login with the same password
Option 2: Backup the SQL Server Login on the old SQL Server and transport to the new server
Option 3: Use Windows Authenticated Accounts/Groups
Option 4: Use some 3rd party tool
Option 5: Relink the SQL Server Login with the Database User
Drawback of option 1, as you have already noticed, is that the SID of the newly created SQL Server Login will be different than the SID of the SQL Server Login from the old server if the Login is a native (non-Window-AD) login.
When you restore the database backup, the SQL Server instance can not match the SID in the master database, with the SID in the restored database (because that's how it's done) and will not relink the SQL Server Login to the Database User.
However, option 2 (backing up and copying the old SQL Server Login to the new server) will retain the SID of the native SQL Server Login and on restore of the database will automatically relink the SQL Server Login with the Database User.
Database User are stored in
<DATABASE_NAME>.sys.database_principals and SQL Server Logins are stored in
master.sys.server_principals. You can look at the SID in both tables and verify that the SIDs of the same named SQL Server Logins will vary if you chose option 1.
Option 3. Use Windows Authenticated Accounts / Groups. The SID will not vary, because it is retrieved from Active Directory and will always work after a database restore, if the SQL Server is in the same domain.
Option 4. Use the
Export-DbaLogin command from the DBATools.IO Powershell Toolbbox, or some other third-party tool.
Option 5 Manually relink the SQL Server Login with the Database User using the deprecated
sp_change_users_login command, or use the
ALTER USER ... command like:
ALTER USER database_user WITH LOGIN sql_server_login;
The public role is a built-in role and can't be assigned directly (explicit) it is assigned automatically, when a SQL Server Login is linked to a Database User:
Every database user belongs to the public database role. When a user hasn't been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Database users cannot be removed from the public role.
Reference: Database Level Roles - Public Role (Microsoft | SQL Docs)