Adding Bulk Rights SQL 2005

Was trying to do some quick administration and realized the SQL Server 2005 had a different method than 2012. Do not put any [] brackets in the user name. However, make sure you put brackets around the DB name especially with Sharepoint to handle the dashed in the DB name.


DECLARE @cmd nvarchar(255);

--first create user
SET @cmd = 'IF ''[?]'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN USE [?] CREATE USER [domain\user] FOR LOGIN [domain\user]''  END';

EXEC master.sys.sp_MSforeachdb  @command1 = @cmd

--second assign role
SET @cmd = 'IF ''[?]'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN USE [?] exec sp_addrolemember ''db_owner'', ''Domain\User''  END';

EXEC master.sys.sp_MSforeachdb  @command1 = @cmd

Clear Temp DB without restart

Doesn't always work but sometimes magic happens. This is essentially other peoples work put here so I remember.


use tempdb
GO

DBCC FREEPROCCACHE -- clean cache
DBCC DROPCLEANBUFFERS -- clean buffers
DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
DBCC FREESESSIONCACHE -- clean session cache
DBCC SHRINKDATABASE(tempdb, 20); -- shrink tempdb
DBCC shrinkfile ('tempdev') -- shrink db file
DBCC shrinkfile ('templog') -- shrink log file
GO

-- report the new file sizes
SELECT name, size
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO