生成数据库角色权限

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 status<>4260872
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