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, @ne
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
