Return to Answer

formatted answer;

Source Link

edited Jan 7, 2022 at 11:39

18.3k
14
54
115

A SQL Server Login authenticates against the SQL Server. ItAfter 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.

When you restore If a database from one server to another then you are transporting the Database Users within the backup. Howeverlink exists, then the SQL Server Logins are not transported withLogin is able to modify data in the database backupvie 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.

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

Things To Look Atpublic Role

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)}

Reference Reading

SQL Server Logins (Microsoft | SQL Docs)
Create a Database User (Microsoft | SQL Docs)
How to Back Up and Restore SQL Server Logins (Microsoft)
database.ioDBA Tools (DBATools.IO)

Database Level Roles - Public Role (Microsoft | SQL Docs)

A SQL Server Login authenticates against the SQL Server. It the 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.

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.

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

Things To Look At

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.

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

public Role

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)}

Reference Reading

SQL Server Logins (Microsoft | SQL Docs)
Create a Database User (Microsoft | SQL Docs)
How to Back Up and Restore SQL Server Logins (Microsoft)
DBA Tools (DBATools.IO)

Database Level Roles - Public Role (Microsoft | SQL Docs)

Source Link

created Jan 7, 2022 at 11:17

John K. N.

18.3k
14
54
115

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. It the 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.

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.

Additional Info

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;