This script will:
1. Change the DB owner
2. Set all to simple mode. Cannot figure out how to stop the tempdb message
3. Set all DBs to 2012 mode
4. Add the domain users as owners. Please use this as a prototype for other test and dev users
5. Shrink all logs to 500 MB to ensure there is plenty of space
Left to do standardize file growths, block create user when the user exists
USE Master
GO
DECLARE @prefix nvarchar(120) = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN ';
DECLARE @postfix nvarchar(50) = ' END';
DECLARE @cmd nvarchar(512);
SET @cmd = @prefix + 'PRINT ''?''; USE ? exec sp_changedbowner sa ' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb @command1 = @cmd
PRINT 'Successfully set DB owner to sa';
SET @cmd = @prefix + 'ALTER DATABASE ? SET RECOVERY SIMPLE;' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb @command1 = @cmd;
PRINT 'Set all DBs to SIMPLE';
SET @cmd = @prefix + 'ALTER DATABASE ? SET COMPATIBILITY_LEVEL = 110;'+ @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb @command1 = @cmd;
PRINT 'Upgrade all DBs to 2012';
SET @cmd = @prefix + 'Use ?; CREATE USER [[DOMAIN]\[USER]] WITH DEFAULT_SCHEMA = dbo;' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb @command1 = @cmd;
PRINT 'Add group [DOMAIN]\[USER]';
SET @cmd = @prefix + 'Use ?; ALTER ROLE db_owner ADD MEMBER [[DOMAIN]\[USER]];' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb @command1 = @cmd;
PRINT 'Add db_owner [DOMAIN]\[USER]';
SET @cmd = @prefix + 'Use ?; DBCC SHRINKFILE (N''?_log'' , 500);' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb @command1 = @cmd;
PRINT 'Shrink all logs to 500 MB';
