/************************************************************************************************/
/*												*/
/*	Script to generate sp_grantdbaccess and sp_addrolemember scripts 			*/
/*												*/
/*	Run this in a database before it is restored from a different source to 		*/
/*	produce scripts	that will add users and roles back in after the restore			*/
/*												*/
/*	Author: Mike Leaman									*/
/*	Date  : 19th April 2001									*/
/*												*/
/************************************************************************************************/

--grants db access to all users who are in database and have a valid login on the SQL server
select 'EXEC sp_grantdbaccess '''+rtrim(sl.loginname)+''','''+rtrim(su.name)+'''' 
from sysusers su join master.dbo.syslogins sl
on su.sid = sl.sid
where su.issqlrole = 0 
and su.sid != 0x01
order by su.name 

--Adds users who have legitimate db access into roles they were in
select 'EXEC sp_addrolemember '''+rtrim(su2.name)+''','''+rtrim(der1.username)+''''
from sysusers su2 join
(select sm.groupuid as groupid,su.name as username from 
sysmembers sm join sysusers su 
on sm.memberuid = su.uid
where su.issqlrole = 0 and su.sid != 0x01) der1
on su2.uid = der1.groupid
where su2.issqlrole = 1