SELECT
dp.class_desc, dp.permission_name, dp.state_desc,
ObjectName = OBJECT_NAME(major_id), GranteeName = grantee.name, GrantorName = grantor.name
FROM sys.database_permissions dp
JOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id
JOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Email_Profile',
@principal_name = 'UserORrole'

use master
go
sp_configure ''show advanced options'',1
go
reconfigure with override
go
sp_configure ''Database Mail XPs'',1
--go
--sp_configure ''SQL Mail XPs'',0
go
reconfigure
go

declare @BodyText nvarchar(max)

SET @BodyText = 'Attached is the quarterly audit.

Regards,

SQL Administrators'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Mail',
@recipients='joe.schmoe@company.com; billy.bob@company.com',
@copy_recipients='admins@company.com',
@subject='Quarterly Audit',
@body=@BodyText,
@query='USE Product_Database
			SELECT SUBSTRING(description, 1, 30) as Name,
			CASE
				WHEN privilege = ''1''
					THEN (''Admin'')
				WHEN privilege = ''0''
					THEN (''System User'')
				ELSE ''''
				END AS ''Role''
			FROM [dbo].[user_table]
			WHERE enabled = ''1''
			AND item_type = ''7765''
		ORDER BY Role'
,@attach_query_result_as_file = 1
,@query_attachment_filename = 'User_Accts.txt'
,@exclude_query_output = 1;