Hello NG Users!
I have a MS SQL Server2000 SP3 in Office and 50 Notebook-users with
MSDE 2000 SP3. I must copy role of server on Notebooks. I make by DTS
Wizard, but copying process breaks off with error, although the role
is koriert. On Notebook role is indicated, but all object with
permissions fot this role do not participate. Alse my user may make
everything and that can be. Which wrong I make. Do you have any ideas?
I thank you in advance.

Dmitri

Here is a script that will create a script of existing permissions.



if object_id('sp_list_permissions') is not null
drop procedure sp_list_permissions
go

create procedure sp_list_permissions (@dbname varchar(30) = NULL)
as
-- -------------------------------------------------------------------
-- sp_list_permissions
-- List all permissions granted on Tables, Views and Stored Procedures
-- Parameters: @dbname - if NULL, defaults to all databases
--
-- Created by Craig Chvatal
-- Created on: 9/17/1999
-- -------------------------------------------------------------------
set nocount on
--declare @dbname varchar(30)
declare @printline varchar(80)
declare @sql varchar(255)
declare @sql1 varchar(255)

if @dbname is NULL
begin
declare dbcursor cursor
for select name from master..sysdatabases
where name not in ('master','msdb','pubs','model')

open dbcursor
fetch next from dbcursor into @dbname
while (@@FETCH_STATUS <> -1)
begin
print '------------------------------------------------'
select @printline = "use " + @dbname
print @printline
print "go"

select @sql = ' select "grant " + case spt.action '
select @sql = @sql + ' when 26 then "REFERENCES " when 193 then "SELECT "'
select @sql = @sql + ' when 195 then "INSERT " when 196 then "DELETE "'
select @sql = @sql + ' when 197 then "UPDATE " when 224 then "EXECUTE "
end +'
select @sql = @sql + ' " ON " + rtrim(so.name)'
select @sql1 = '+" TO " + (select su.name from ' + rtrim(@dbname) +
'..sysusers su where '
select @sql1 = @sql1 + 'su.uid = spt.uid) + char(13) + "go" from ' +
rtrim(@dbname) + '..sysobjects so '
select @sql1 = @sql1 + 'join ' + rtrim(@dbname) + '..sysprotects spt on
so.id = spt.id'
select @sql1 = @sql1 + ' where so.type in ("P","U","V") '
--print @sql
--print @sql1

exec('use ' + @dbname + @sql + @sql1)
print 'go'
fetch next from dbcursor into @dbname
end
close dbcursor
deallocate dbcursor
end
else
begin
print '------------------------------------------------'
select @printline = "use " + @dbname
print @printline
print "go"

select @sql = ' select "grant " + case spt.action '
select @sql = @sql + ' when 26 then "REFERENCES " when 193 then "SELECT "'
select @sql = @sql + ' when 195 then "INSERT " when 196 then "DELETE "'
select @sql = @sql + ' when 197 then "UPDATE " when 224 then "EXECUTE "
end +'
select @sql = @sql + ' " ON " + rtrim(so.name)'
select @sql1 = '+" TO " + (select su.name from ' + rtrim(@dbname) +
'..sysusers su where '
select @sql1 = @sql1 + 'su.uid = spt.uid) from ' + rtrim(@dbname) +
'..sysobjects so '
select @sql1 = @sql1 + 'join ' + rtrim(@dbname) + '..sysprotects spt on
so.id = spt.id'
select @sql1 = @sql1 + ' where so.type in ("P","U","V") '
--print @sql
--print @sql1

exec('use ' + @dbname + @sql + @sql1)
print 'go'
end
set nocount off
go


--

Andrew J. Kelly
SQL Server MVP


"Dmitri" <dmitrip (AT) web (DOT) de> wrote