USE [TfsVersionControl]
GO
/****** Object: StoredProcedure [dbo].[usp_AutoSendUserPrjRight] Script Date: 09/06/2010 10:33:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[usp_AutoSendUserPrjRight] As
Begin
/*
Function : 定期向配置管理员发送用户权限列表
Author: LuoHP
Date:20100904
Note: First Create SQLMail
*/
declare @BodyStr nvarchar(max)
Declare @QuerySQL nvarchar(max)
Select @BodyStr = Convert(nvarchar(16),getdate(),120)+' List as a attachment'
--表说明 Begin
--TfsIntegration.dbo.tbl_gss_group_membership 用户群组关系表
--TfsIntegration.dbo.tbl_security_identity_cache 用户群组信息描述 type=2 用户 type =4 群组
--TfsIntegration.dbo.tbl_projects 项目信息表
--表说明 End
Set @QuerySQL= 'Select Convert(nvarchar(20),A.Account_Name) As [User],Convert(nvarchar(50),B.Display_Name) As Role,Convert(nvarchar(50),D.[project_name]) As Prj
From [tbl_security_identity_cache] A
Left Join [tbl_gss_group_membership] C On C.[member_sid] = A.sID
Left Join [tbl_security_identity_cache] B On B.SID = C.[parent_group_sid]
And B.Type=4 and A.deleted=0
Left Join [tbl_projects] D On Convert(nvarchar(40),D.Project_ID) = replace(B.[domain],''vstfs:///Classification/TeamProject/'','''')
Where A.Type = 2 and A.deleted=0
And B.Display_Name is not Null
And A.Display_Name Not in(''TFSSERVICE'',''TFSSETUP'')
Order By D.[project_name],B.Display_Name'
--Print @QuerySQL
--取得收件人列表
declare @Mail nvarchar(max)
set @Mail = 'username@hotmail.com‘
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'mailProfile',--数据库邮件配置文件
@recipients = @mail,
@subject = '【TFS每周提示】TFS用户权限记录检查',
@body = @BodyStr,
@attach_query_result_as_file =1,--查询结果作为附件，否则访问邮件正文
@query = @QuerySQL,
@append_query_error=1,--查询出错时，错误信息通过邮件正文发送出来
@execute_query_database ='TfsIntegration'
End
