-- Script to generate grants from SQL Server 7.0 databases.
-- change to use correct database for script and save output.
use master
go
declare @Servername varchar(32)
select @Servername = (select @@servername)
PRINT '-- '+@Servername
go
set nocount on
go
use [ABACUS]
print 'use ABACUS'
print 'go'
go
print ' '
print '-- Execute grants for stored procedures'
print ' '
select 'Grant Execute on ' + o.name + '   to   '+ u.name 
 from sysusers u,syspermissions p,sysobjects o
 where p.grantee = u.uid
   and p.id = o.id
   and (u.issqlrole = 1 or u.issqlrole = 0)
   and (32 = (p.actadd&32))
 order by u.name,o.name
go
print ' '
print '-- Select grants for Tables and Views'
print ' '
select 'Grant Select on ' + o.name + '   to   '+ u.name 
 from sysusers u,syspermissions p,sysobjects o
 where p.grantee = u.uid
   and p.id = o.id
   and (u.issqlrole = 1 or u.issqlrole = 0)
   and (1 = (p.actadd&1))
 order by u.name,o.name
go
print ' '
print '-- Update grants for Tables and Views'
print ' '
select 'Grant Update on ' + o.name + '   to   '+ u.name 
 from sysusers u,syspermissions p,sysobjects o
 where p.grantee = u.uid
   and p.id = o.id
   and (u.issqlrole = 1 or u.issqlrole = 0)
   and (2 = (p.actadd&2))
 order by u.name,o.name
go
print ' '
print '-- Insert grants for Tables and Views'
print ' '
select 'Grant Insert on ' + o.name + '   to   '+ u.name 
 from sysusers u,syspermissions p,sysobjects o
 where p.grantee = u.uid
   and p.id = o.id
   and (u.issqlrole = 1 or u.issqlrole = 0)
   and (8 = (p.actadd&8))
 order by u.name,o.name
go
print ' '
print '-- Delete grants for Tables and Views'
print ' '
select 'Grant Delete on ' + o.name + '   to   '+ u.name 
 from sysusers u,syspermissions p,sysobjects o
 where p.grantee = u.uid
   and p.id = o.id
   and (u.issqlrole = 1 or u.issqlrole = 0)
   and (16 = (p.actadd&16))
 order by u.name,o.name
go
print ' '
print '-- end of grants'
print ' '
go
print '-- DRI set for Tables and views'
print ' '
print ' DRI'
select 'Grant DRI on ' + o.name + '   to   '+ u.name 
 from sysusers u,syspermissions p,sysobjects o
 where p.grantee = u.uid
   and p.id = o.id
   and o.id = 117575457
   and (4 = (p.actadd&4))
 order by u.name,o.name
go