We are doing a conversion from one system to another which is requiring backup/restores. I am trying to create a process that will remove all users after the restore on the new servers as no user permissions will follow the database to the new server. I have it working for a single database, but when I try and loop through all databases and run my process I have issues. Does anyone have suggestions on how I can enhance my script to get the results I am looking for.
DECLARE @SqlCommand VARCHAR(4000) DECLARE @MaxRowNum INT DECLARE @MinRowNum INT DECLARE @DatabaseCount INT DECLARE @MaxDatabaseCount INT DECLARE @DatabaseName VARCHAR(50) SELECT RowNum = ROW_NUMBER() OVER (ORDER BY Name), [name] INTO #DatabaseList FROM sys.databases WHERE database_id > 4 AND [name] NOT IN ('DBAdmin', 'FS_TEST', 'Garry') ORDER BY [name] SELECT @DatabaseCount = (SELECT MIN(RowNum) FROM #DatabaseList), @MaxDatabaseCount = (SELECT MAX(RowNum) FROM #DatabaseList), @DatabaseName = DB_NAME() WHILE @DatabaseCount <= @MaxDatabaseCount BEGIN SELECT @SqlCommand = 'USE [' + name + ']' + CHAR(13) + 'GO'FROM #DatabaseList WHERE RowNum = @DatabaseCount EXEC (@SqlCommand) SELECT RowNum = ROW_NUMBER() OVER (ORDER BY Name), [name] INTO #UserList FROM sys.database_principals where (type_desc like '%use%' OR type_desc LIKE '%group%') and name not in('dbo','guest','INFORMATION_SCHEMA','sys','public') SELECT @MinRowNum = (SELECT MIN(RowNum) FROM #UserList), @MaxRowNum = (SELECT MAX(RowNum) FROM #UserList), @DatabaseName = DB_NAME() WHILE @MinRowNum <= @MaxRowNum BEGIN SELECT @SqlCommand = 'DROP USER [' + name + ']' FROM #UserList WHERE RowNum = @MinRowNum print (@SqlCommand) PRINT (@SqlCommand + ' --- User Dropped from ' + @DatabaseName) SET @MinRowNum = @MinRowNum + 1 END SET @DatabaseCount = @DatabaseCount + 1 DROP TABLE #UserList END DROP TABLE #DatabaseList