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