Stored Procedure

Sysmail_add_principalprofile_sp – SQL Server Database Mail Stored Procedure – Part 9

Hi friends, in this blog of SQL Server Database mail stored procedure 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s