愛比的新手筆記

--有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';
DECLARE	@sql VARCHAR(Max)
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--補上帳號
		SET @sql = 'CREATE LOGIN '+@USER+' WITH PASSWORD=N''密碼'', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
		exec (@sql)
		print '*create user ' + @USER
	END

	EXEC sp_change_users_login 'update_one',@USER,@USER
	PRINT @USER + ' link to DB user reset';
    SET @loop = @loop + 1
  END
END
SET NOCOUNT OFF
EXEC sp_change_users_login 'Report';

DROP TABLE #orphaned

 

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