Hi friends, in this blog of SQL Server system stored procedure series we will continue with another database mail procedure sysmail_add_principalprofile_sp.
Sysmail_add_principalprofile_sp stored procedure grants permission to a database user or role to use a database mail profile.
Syntax
sysmail_add_principalprofile_sp { [ @principal_id = ] principal_id | [ @principal_name = ] 'principal_name' } ,
{ [ @profile_id = ] profile_id | [ @profile_name = ] 'profile_name' }
[ , [ @is_default ] = 'is_default' ]
Arguments
@principal_Id – ID of the database user or role in msdb database. A principal_id with 0 makes it public profile which grants access to all principals in the database.
@principal_name – Name of the database user or role in msdb database. Either @principal_id or @principal_name is necessary to specify. A profile_name with ‘Public’ makes it public profile which grants access to all principals in the database.
@profile_id – Id of the database mail profile.
@profile_name – Name of the database mail profile. Either @profile_id or @profile_name must be specified.
@is_default – Specify whether the profile is default profile for the principal. A principal must have one default profile.
Now let’s execute the below code to make the profile ‘Database Test Mail’ the default public profile for all users in msdb database.
EXEC msdb.dbo.sysmail_add_principalprofile_sp @principal_name = 'Public', @profile_name = 'Database Test Mail', @is_default = 1
That’s all folks for the day.
In next blog we will learn how to get the information about principal profile which we created today.
