/*Get Database Cursor*/
declare recscan cursor for
select name 
from sys.databases
where name not in ('master','tempdb','model','msdb')


/*declare variables*/
declare @db varchar(100)
declare @sql varchar(4000)
declare @user varchar(100)
declare @roles varchar(max)
declare @role varchar(50)
declare @winuser bit
declare @message varchar(max)

/*string parsing variables*/
declare @strpos int
declare @strlen int
set @strpos = 0

	
/*Set user name and roles to be assigned, comma separated string.
@winuser defines if user is windows(1) or not(0).*/

set @winuser = 0
set @user = <<USER NAME>> /*User name, such as DOMAIN\msfal for windows or report_reader for direct db login*/
set @roles = <<Comma seperated role list>> /*i.e.'db_datareader,db_datawriter'

/*create server login*/
Print 'Creating user ['+@user+'] on server...'
if @winuser = 1
begin
	set @sql= 'USE [master] CREATE LOGIN ['+@user+'] FROM WINDOWS WITH DEFAULT_DATABASE=[master]'
end
else
	set @sql= 'USE [master] CREATE LOGIN ['+@user+'] WITH PASSWORD = '''+@user+'!123'''
exec(@sql)

/*Cycle through DBs to assign roles.*/
open recscan
fetch next from recscan into @db
while @@fetch_status = 0
begin
	/*Add user if user does not exist in database.*/
	print 'Adding ['+@user+'] to ['+@db+']...'
	set @sql = 'USE ['+@db+'] CREATE USER ['+@user+'] FOR LOGIN ['+@user+']' 
	exec(@sql)

	/*Parse role string and assign each role to the user.*/
	Print @db + char(10) + '****************************'
	while @strpos < LEN(@roles)
	begin
		select @strlen = case when charindex(',',@roles,@strpos) = 0 then LEN(@roles)-@strpos+1 else charindex(',',@roles,@strpos) -@strpos end
		select @role = SUBSTRING(@roles,@strpos,@strlen)
		
		print char(9)+'Adding '+@user+' to '+@role+' role in '+@db+'...'
		set @sql = 'USE ['+@db+'] EXEC sp_addrolemember N'''+@role+''', N'''+@user+''''
		exec(@sql)
		select @strpos = case when charindex(',',@roles,@strpos) = 0 then LEN(@roles) else charindex(',',@roles,@strpos) + 1 end
	end

print char(10)
fetch next from recscan into @db
set @strpos = 0
end

/*clean up cursor*/
close recscan
deallocate recscan
