Sysmail_add_principalprofile_sp – Day 18 – SQL Server System Stored Procedure

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.

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kapil Singh Kumawat

Kapil Singh Kumawat has been working with SQL Server since last 5 years. He is from Jaipur, Rajasthan, India and currently working with Cognizant Technology Solutions as SQL Server Developer. He has good experience in performance tuning, SSIS, data migration and data designing. Apart from database he has interest in travelling, watching football and listening music.

View all posts by Kapil Singh Kumawat →

Leave a Reply

Your email address will not be published. Required fields are marked *