生成数据库角色权限

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