print '--#################################################################'
print '--Generate Script for Database Users/Roles'
print '--#################################################################'
print ''

/****************************************
Script Made by Lester A. Policarpio
For questions and clarifications feel free to email me at
lpolicarpio2005@yahooo.com
*/
DECLARE @dbcomp varchar(1024)
DECLARE @pass varchar(5000)
DECLARE @counter varchar(500)

DECLARE @dbid varchar(100)

CREATE TABLE DBROLES 
( DBName sysname not null, 
 UserName sysname not null, 
 db_owner varchar(3) not null,
 db_accessadmin varchar(3) not null,
 db_securityadmin varchar(3) not null,
 db_ddladmin varchar(3) not null,
 db_datareader varchar(3) not null,
 db_datawriter varchar(3) not null,
 db_denydatareader varchar(3) not null,
 db_denydatawriter varchar(3) not null,
 db_backupoperator varchar(3) not null
)



declare @dbname varchar(200)

declare @mSql1 varchar(8000)

DECLARE DBName_Cursor CURSOR FOR 
select name from master.dbo.sysdatabases where name not in ('master','tempdb','model','pubs','northwind','DBA','msdb') Order by name
OPEN DBName_Cursor

FETCH NEXT FROM DBName_Cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

 Set @mSQL1 = 'Insert into DBROLES ( DBName, UserName, db_owner, db_accessadmin, 
 db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
 db_denydatareader, db_denydatawriter,db_backupoperator ) SELECT '+''''+ @dbName +''''+ ' as DBName ,UserName, '+char(13)+' Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter,
Max(CASE RoleName WHEN ''db_backupoperator'' THEN ''Yes'' ELSE ''No'' END) AS db_backupoperator
from ( select b.name as USERName, c.name as RoleName
from ' + @dbName+'.dbo.sysmembers a '+char(13)+' join '+ @dbName+'.dbo.sysusers b '+char(13)+' on a.memberuid = b.uid join '+@dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName order by UserName'

 Print @mSql1
 Execute (@mSql1)

 FETCH NEXT FROM DBName_Cursor INTO @dbname
END

CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor

DECLARE @db varchar(1024)
DECLARE @name1 varchar(200)
DECLARE @name2 varchar(20)
DECLARE @hasdbaccess varchar(200)
DECLARE @islogin varchar(200)
DECLARE @isntname varchar(200)
DECLARE @isntgroup varchar(200)
DECLARE @isntuser varchar(200)
DECLARE @issqluser varchar(200)
DECLARE @isaliased varchar(200)
DECLARE @issqlrole varchar(200)
DECLARE @isapprole varchar(200)


SET @name2 = '1'

DECLARE cur CURSOR FOR
select dbname,username,db_owner,db_accessadmin,db_securityadmin,db_ddladmin,db_datareader,db_datawriter,
db_denydatareader,db_denydatawriter,db_backupoperator from DBROLES WHERE username <> 'DBO'
OPEN cur
FETCH NEXT FROM cur INTO @counter,@name1,@hasdbaccess,@islogin,@isntname,@isntgroup,@isntuser,@issqluser,@isaliased,@issqlrole,@isapprole
WHILE (@@FETCH_STATUS = 0)
BEGIN
print '--@@@@@'+@name1+@name2+'@@@@@--'
print 'DECLARE @'+@name1+@name2+' varchar(1024)'
print 'DECLARE @'+@name1+@name2+'2 varchar(1024)'
print 'DECLARE @'+@name1+@name2+'3 varchar(1024)'
print 'DECLARE '+@name1 +@name2+ ' CURSOR for'
print 'select name from master..sysdatabases where name IN ('+''''+@counter+''''+')'
print 'OPEN '+@name1+@name2
print 'FETCH NEXT FROM '+@name1+@name2+' INTO @'+@name1+@name2
print 'WHILE (@@FETCH_STATUS = 0)'
print 'BEGIN'
print 'SET @'+@name1+@name2+'2 = @'+@name1+@name2+'+'+''''+'..sp_grantdbaccess '+''''+'+'+''''+@name1+''''
print 'EXEC (@'+@name1+@name2+'2)'

-- @hasdbaccess
IF (@hasdbaccess = 'YES')
BEGIN
print 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_owner'+','+@name1+''''
print 'EXEC (@'+@name1+@name2+'3)'
END

-- @islogin
IF (@islogin ='YES')
BEGIN
print 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_accessadmin'+','+@name1+''''
print 'EXEC (@'+@name1+@name2+'3)'
END

-- @isntname
IF (@isntname = 'YES')
BEGIN
print 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_securityadmin'+','+@name1+''''
print 'EXEC (@'+@name1+@name2+'3)'
END

-- @isntgroup
IF (@isntgroup ='YES')
BEGIN
print 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_ddladmin'+','+@name1+''''
print 'EXEC (@'+@name1+@name2+'3)'
END

-- @isntuser
IF (@isntuser = 'YES')
BEGIN
print 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_datareader'+','+@name1+''''
print 'EXEC (@'+@name1+@name2+'3)'
END

-- @issqluser
IF (@issqluser = 'YES')
BEGIN
print 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_datawriter'+','+@name1+''''
print 'EXEC (@'+@name1+@name2+'3)'
END

-- @isaliased
IF (@isaliased = 'YES')
BEGIN
print 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_denydatareader'+','+@name1+''''
print 'EXEC (@'+@name1+@name2+'3)'
END

-- @issqlrole
IF (@issqlrole = 'YES')
BEGIN
print 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_denydatawriter'+','+@name1+''''
print 'EXEC (@'+@name1+@name2+'3)'
END

-- @isqpprole
IF (@isapprole = 'YES')
BEGIN
print 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_backupoperator'+','+@name1+''''
print 'EXEC (@'+@name1+@name2+'3)'
END


print 'FETCH NEXT FROM '+@name1+@name2+' INTO @'+@name1+@name2
print 'END'
print 'CLOSE '+@name1+@name2
print 'DEALLOCATE '+@name1+@name2

SET @name2 = @name2+'1'

FETCH NEXT FROM cur INTO @counter,@name1,@hasdbaccess,@islogin,@isntname,@isntgroup,@isntuser,@issqluser,@isaliased,@issqlrole,@isapprole
END
CLOSE cur
DEALLOCATE cur



DROP TABLE DBROLES