use public_data
go
/****** object: storedprocedure [dbo].[p_copyuserpermission] script date: 01/19/2011 11:09:13 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure [dbo].[p_copyuserpermission]
(@username sysname,
@newusername sysname=null)
as
set nocount on
begin
if @newusername is null
set @newusername=@username
if (select object_id('tempdb..#tt')) is not null
drop table #tt
create table #tt
(owner sysname,
object sysname,
grantee sysname,
grantor sysname,
protecttype varchar(10),
actionname varchar(20),
columnname sysname
)
if (select object_id('tempdb..#t2')) is not null
drop table #t2
create table #t2
(sql varchar(max)
)
declare @db sysname
declare cu_listuserpermission cursor for
select name from master..sysdatabases where name not like 'dbss%'
and status4260872
open cu_listuserpermission
fetch next from cu_listuserpermission into @db
while @@fetch_status=0
begin
begin try
insert #tt execute sp_helprotect @username = @username
insert #t2
select 'use '+@db
union all
select '
if not exists(select * from sysusers where name='''+@newusername+''')'
union all
select 'begin'
union all
select ' create user ['+@newusername+'] for login ['+@newusername+'] with default_schema=[dbo]'
union all
select 'end '
union all
select
distinct rtrim(protecttype) + ' ' + actionname + '' +
case object when '.' then '' else ' on ' + '['+owner+'].['+object+']' +
case when columnname in('(all+new)','(all)','(new)','.') then '' else '('+columnname+')' end end
+' to ' + @newusername
from #tt
union all
select 'exec sp_addrolemember ''' +roles.name+''','''+@newusername+''''
from sysusers users, sysusers roles, sysmembers members
where roles.uid = members.groupuid
and roles.issqlrole = 1
and users.uid = members.memberuid
and users.name = @username
end try
begin catch
end catch
fetch next from cu_listuserpermission into @db
end
close cu_listuserpermission
deallocate cu_listuserpermission
select * from #t2
end