Send the TFS Team Project user permission check information automatically

 

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]
Begin
/*
Function: periodically sends the user permission list to the configuration administrator.
Author: luohp
Data: 20100904
Note: first create sqlmail
*/
Declare @ bodystr nvarchar (max)
Declare @ querysql nvarchar (max)
Select @ bodystr = convert (nvarchar (16), getdate (), 120) + 'list as a attachment'
-- Table description begin
-- Tfsintegration. DBO. tbl_gss_group_membership user group relationship table
-- Tfsintegration. DBO. tbl_security_identity_cache User Group Information Description type = 2 user type = 4 group
-- Tfsintegration. DBO. tbl_projects project information table
-- Table description end

Set @ querysql = 'select convert (nvarchar (20),. 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]
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'', ''tfsetup '')
Order by D. [project_name], B. display_name'

-- Print @ querysql

-- Retrieve the recipient list
Declare @ mail nvarchar (max)
Set @ mail = 'username @ hotmail.com'

Exec MSDB. DBO. sp_send_dbmail
@ Profile_name = 'mailprofile ', -- database mail configuration file
@ Recipients = @ mail,
@ Subject = '[TFs weekly prompt] TFs user permission record check ',
@ Body = @ bodystr,
@ Attach_query_result_as_file = 1, -- the query result is used as an attachment; otherwise, the email body is accessed.
@ Query = @ querysql,
@ Append_query_error = 1, -- when an error occurs in the query, the error message is sent by the mail body.
@ Execute_query_database = 'tfsintegration'

End