[SQL] 重新附加DB後重對應帳號

  • 21
  • 0
  • SQL
  • 2020-01-17

重新附加或置換MDF檔後重對應帳號的語法

--有restore的db都要run
SET NOCOUNT ON
DECLARE @loop INT
DECLARE @USER sysname

CREATE TABLE #orphaned (UserName sysname, UserSID VARBINARY(85),IDENT INT IDENTITY(1,1))

INSERT INTO #orphaned
EXEC sp_change_users_login 'report';

select * from #orphaned
IF(SELECT COUNT(*) FROM #orphaned) > 0
BEGIN
 SET @loop = 1
 WHILE @loop <= (SELECT MAX(IDENT) FROM #orphaned)
  BEGIN
    SET @USER = (SELECT UserName FROM #orphaned WHERE IDENT = @loop)
    IF(SELECT COUNT(*) FROM sys.server_principals WHERE [Name] = @USER) >0
     BEGIN
		EXEC sp_change_users_login 'update_one',@USER,@USER
		PRINT @USER + ' link to DB user reset';
     END     
    SET @loop = @loop + 1
  END
END
SET NOCOUNT OFF
EXEC sp_change_users_login 'Report';

DROP TABLE #orphaned

 

Taiwan is a country. 臺灣是我的國家