SQL Server中生成某一用户的权限授权脚本

CREATE procedure ListUserPermission
(@UserName sysname)
AS
set nocount on
BEGIN
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
)
insert #tt execute sp_helprotect @username = @UserName
select
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 ‘ + grantee
from #tt
union all
SELECT ‘EXEC sp_addsrvrolemember ”’+Users.name+”’,”’ +Roles.Name+””
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
GO