DECLARE @role_name VARCHAR(100)
DECLARE @member_name VARCHAR(100)
DECLARE @rolescur CURSOR
DECLARE @rolememberscur CURSOR

DECLARE @loginname VARCHAR(100)
DECLARE @username VARCHAR(100)
DECLARE @logincur CURSOR

PRINT '– Grant user access'
SET @logincur = CURSOR FOR SELECT l.name, u.name
FROM master..sysxlogins l, sysusers u
WHERE l.sid = u.sid AND l.name <> 'sa'
OPEN @logincur
FETCH NEXT FROM @logincur INTO @loginname, @username
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'EXEC sp_grantdbaccess ''' + @loginname + ''', ''' + @username + ''''
FETCH NEXT FROM @logincur INTO @loginname, @username
END
CLOSE @logincur
DEALLOCATE @logincur

— cursor for roles (user and built-in)
SET @rolescur = CURSOR FOR SELECT name FROM dbo.sysusers WHERE ([issqlrole] = 1 OR [isapprole] = 1) AND name <> 'db_owner'

— loop through roles
OPEN @rolescur
FETCH NEXT FROM @rolescur INTO @role_name
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT '— ' + @role_name
— cursor for members in the roles
SET @rolememberscur = CURSOR FOR SELECT u.name
from sysusers u, sysusers g, sysmembers m
where g.name = @role_name
and g.uid = m.groupuid
and g.issqlrole = 1
and u.uid = m.memberuid

OPEN @rolememberscur
FETCH NEXT FROM @rolememberscur INTO @member_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'exec sp_addrolemember ''' + @role_name + ''', ''' + @member_name + ''''
FETCH NEXT FROM @rolememberscur INTO @member_name
END
CLOSE @rolememberscur
DEALLOCATE @rolememberscur

— PRINT @role_name
FETCH NEXT FROM @rolescur INTO @role_name
END

CLOSE @rolescur
DEALLOCATE @rolescur

• Grant