In the SharePoint business administrating the SharePoint farm often involves also taking care of the databases SharePoint runs on. Even if your organisation employs dedicated DBAs often the limitations of what you can do with the databases and the procedures for backup and restore will have the SharePoint administrators involved at least partly with the creation of procedures for backup and restore and regular maintenance jobs.
For testing purposes, for example performance tests or granular restores with the SQL server backup and restore functions sometimes will involve restoring the SharePoint databases to another SQL Server instance or a different server even. In these scenario’s when you have restored the database, the database users will be orphaned, even if the SQL login names on the new server are exactly the same they will still become orphaned. This is caused due to the fact that the database users are identified with SID’s and when you restore a database to another server or instance these SID’s no longer match with the SID’s of the SQL Server logins in the Master Database.
When you try to reset the permissions for the SQL Server Logins in the database you will recieve an error message and the permissions will not be reset.
Error 15023: User or role <username> already exists in the current database.
This problem can be fixed however by useing Transact-SQL scripts that can help you identify and fix the problem. To identify what users are orphaned you can use the following script, it will return a list of all the database users that are orphaned.
USE <Database Name>
When these users are identified you can match them in the restored database to SQL Server logins with the following script. You can choose any SQL Server Login to match the database user with this method.
USE <Database Name>
@UserNamePattern='<Database User Name>’,
@LoginName='<SQL Server Login Name>’
As an alternative you can also use the following script if the database users in the restored database are named exactly like the SQL Server Logins you want to link.
EXEC sp_change_users_login ‘Auto_Fix’, ‘<User Name>’
This will solve the problem of orphaned users and when adding the database to SharePoint will prevent any Access Denied messages in your Web Application from showing up.