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