Hi folks, in our previous blog of SQL Server system stored procedure we added database mail account to database mail profile. In this blog we will learn how to get information about the accounts associated with database mail profiles.
Sysmail_help_profileaccount_sp stored procedure lists the database mail accounts associated with one or more database profile. When no profile_id or profile_name is specified, stored procedure returns information for all database mail profiles.
Syntax:
sysmail_help_profileaccount_sp
{ [ @profile_id = ] profile_id
| [ @profile_name = ] 'profile_name' }
[ , { [ @account_id = ] account_id
| [ @account_name = ] 'account_name' } ]
Arguments
@profile_id is the profile id of the profile having datatype INT.
@profile_name is the name of the database mail profile. Either profile_id or profile_name must be specified.
@account_id is the account of the account having datatype INT.
@account_name is the name of the database mail account. Either @account_id or @account_name must be specified.
Let’s execute the stored procedure passing profile name or account name:
EXECUTE msdb.dbo.sysmail_help_profileaccount_sp @profile_name = 'Database Test Mail' GO EXECUTE msdb.dbo.sysmail_help_profileaccount_sp @account_name = 'DB_Email_Account' GO
After executing the stored procedure we get following columns in result set:
Profile_Id – Profile Id of the database mail profile.
Profile_name – Profile name of the database mail profile.
Account_Id – Account Id of the database account profile.
Account name – Account name of the database account profile.
That’s all folks for the day 🙂
