If you have trouble because cannot login and cannot accessible after you Restoring a Database, you must change owner and connect between user login with user Database.
The following steps are below :
1. Check the orphaned users used this query:
USE change_db_name>; EXEC sp_change_users_login 'Report' |
2. Change owner of a database:
ALTER AUTHORIZATION ON DATABASE::TO MyLoginUser> |
3. Map the user Database with user login :
SET NOCOUNT ON USE change_db_name> GO DECLARE @loop INT DECLARE @USER sysname DECLARE @sqlcmd NVARCHAR(500) = '' IF OBJECT_ID('tempdb..#Orphaned') IS NOT NULL BEGIN DROP TABLE #orphaned END CREATE TABLE #Orphaned (UserName sysname,IDENT INT IDENTITY(1,1)) INSERT INTO #Orphaned (UserName) SELECT [name] FROM sys.database_principals WHERE [type] IN ('U','S') AND is_fixed_role = 0 AND [Name] NOT IN ('dbo','guest','sys','INFORMATION_SCHEMA') IF(SELECT COUNT(*) FROM #Orphaned) > 0 BEGIN SET @loop = 1 WHILE @loop Note : = Change this value to the Database = Change the value to sa or the Administration of user. |
4. Do the steps point 1 - 3 for all of Database you have.