Posted in Stored Procedure

Sysmail_help_principalprofile_sp – SQL Server Database Mail Stored Procedure – Part 10

Hi friends, in this blog of SQL Server  database mail stored procedure we will continue with another database mail stored procedure sysmail_help_principalprofile_sp.

Sysmail_help_principalprofile_sp stored procedure list the information about associations between database mail profile and database principals.

When sysmail_help_principalprofile_sp stored procedure is executed without any parameters then it returned lists all of the associations in the instance of SQL Server.

Syntax

sysmail_help_principalprofile_sp [ {   [ @principal_id = ] principal_id | [ @principal_name = ] ‘principal_name’ } ]

    [ [ , ] {   [ @profile_id = ] profile_id | [ @profile_name = ] ‘profile_name’ } ]

Arguments

@principal_Id – ID of the database user or role in msdb database.

@principal_name – Name of the database user or role in msdb database. Either @principal_id or @principal_name is necessary to specify.

@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.

Now let’s execute the below stored procedure by passing database profile name in parameters:

EXEC msdb.dbo.sysmail_help_principalprofile_sp @profile_name = ‘Database Test Mail’

sysmail_principalprofile_help

Similarly when we executed this stored procedure without passing any parameters it will return list of all association in instance of SQL Server.

EXEC msdb.dbo.sysmail_help_principalprofile_sp

That’s all friends for the day J

Advertisements
Posted in 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.

Posted in Stored Procedure

Sysmail_help_profileaccount_sp – SQL Server Database Mail Stored Procedure – Part 8

Hi folks, in our previous blog of SQL Server Database Mail 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

sysmail_help_profileaccount

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 friends 🙂

Posted in Stored Procedure

Sysmail_add_profileaccount_sp – SQL Server Database Mail Stored Procedure – Part 7

Hi friends, in last previous blogs we created database mail profile and database mail account. In this blog we will learn how to add database mail account to database mail profile using stored procedure sysmail_add_profileaccount_sp.

Sysmail_add_profileaccount_sp stored procedure is used to add database mail account to a database mail profile. Both the mail account and mail profile should already exist else stored procedure returns an error. Stored procedure sysmail_add_profileaccount_sp is present in msdb database and owned by schema dbo. If current database is not msdb then this procedure should be executed using three part name.

Syntax:

sysmail_add_profileaccount_sp { [ @profile_id = ] profile_id | [ @profile_name = ] ‘profile_name’ } ,

{ [ @account_id = ] account_id | [ @account_name = ] ‘account_name’ }

[ , [ @sequence_number = ] sequence_number ]

Arguments:

@profile_id – Database mail profile id to which mail account added.

@profile_name – Database mail profile name to which mail account added. Either profile_id or profile_name must be specified.

@account_id – Database mail account id to add to mail profile.

@account_name – Database mail account name to add to mail profile. Either account_id or account_name must be specified.

@sequence_number – Sequence number is used to determine in which sequence database mail uses account within the profile.

Now we will add database mail account to database mail profile which we created earlier in our previous blog Sysmail_add_account_sp – SQL Server Database Mail Stored Procedure – Part 5 and Sysmail_add_profile_sp – SQL Server Database Mail Stored Procedure – Part 3.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = ‘Database Test Mail’,

@account_name = ‘DB_Email_Account’,

@sequence_number = 1

After executing this procedure database mail account will be added to the mail profile successfully.

That’s all for the day friends :).

Posted in Development, Stored Procedure

Sysmail_help_Account_sp – SQL Server Database Mail Stored Procedure – Part 6

Hi friends, in this blog we will learn how to get information about database mail account using stored procedure sysmail_hep_account_sp.

Sysmail_help_account_sp stored procedure displays information about database mail account.

When the procedure executes without passing any parameters then it will display all database accounts information.

Syntax of sysmail_help_account_Sp is as follows:

sysmail_help_account_sp [ [ @account_id = ] account_id | [ @account_name = ] ‘account_name’ ]

Arguments

@account_id – Account Id of the database mail account.

@account_name – Account name of the database mail account.

Let’s execute the stored procedure to get the information of the database mail account which we created in our previous blog here. Here we define the account name in the parameter list.

EXEC msdb.dbo.sysmail_help_Account_sp NULL, ‘DB_Email_Account’

sysmail_help_account

After executing the stored procedure we will get the following columns in the result set:

Account_Id – Account Id with datatype Int.

Name – Account Name with datatype sysname.

Description – Description of the account having datatype nvarchar(256).

Email_address – Email address of the sender having datatype nvarchar(128).

Display_name – Display name of the account having datatype nvarchar(128).

Replyto_address – The address where replies to message are sent.

Servertype – Type of email server for mail account.

Servername – Name of email server

Port – Port number of email server

Username – Username to use login to email server.

User_default_credentials – Specifies whether to send the email using SMTP server using credentials of SQL Server database engine.

Enable_ssl – Specifies whether Database email encrypts communication using SSL.

That’s all for the day friends. We will continue with other database mail stored procedures in next blogs. Have a happy learning 🙂

Posted in Development, Stored Procedure

Sysmail_add_account_sp – SQL Server Database Mail Stored Procedure – Part 5

Hi friends, today we will continue with database mail stored procedure and will learn how to create database mail account using stored procedure sysmail_add_account_sp.

Sysmail_add_account_sp stored procedure is used to create database mail account which contains information about SMTP account.

Syntax of sysmail_add_account_sp is as:

sysmail_add_account_sp  [ @account_name = ] ‘account_name’,

    [ @email_address = ] ’email_address’ ,

    [ [ @display_name = ] ‘display_name’ , ]

    [ [ @replyto_address = ] ‘replyto_address’ , ]

    [ [ @description = ] ‘description’ , ]

    [ @mailserver_name = ] ‘server_name’

    [ , [ @mailserver_type = ] ‘server_type’ ]

    [ , [ @port = ] port_number ]

    [ , [ @username = ] ‘username’ ]

    [ , [ @password = ] ‘password’ ]

    [ , [ @use_default_credentials = ] use_default_credentials ]

    [ , [ @enable_ssl = ] enable_ssl ]

    [ , [ @account_id = ] account_id OUTPUT ]

Arguments

@account_name – Name of new database mail account.

@email_address – It is email address which will be used to send the message. Its datatype is nvarchar(128).

@display_name – Display name to use on email-address from this database mail account. Its datatype is nvarchar(128).

@replyto_address – It is email address where reply to messages will be sent under sent to. Its datatype is nvarchar(128).

@description – Description of the database mail account.

@mailserver_name – Name or ip address of the SMTP mail server used for this database mail account.

@mailserver_type – Type of email server with default value ‘SMTP’.

@port – Port number of the email server with default value 25.

@username – Username to use to login on email server. Its datatype is nvarchar(128),

@password – Password used to logon email server. Its datatype is nvarchar(128).

@user_default_credentials – Flag used to specify whether to send the mail to SMTP server using credentials of SQL Server Database Engine or not having default value 0. When flag value is 1, it will use credentials of Database Engine. When flag value 0, it will use credentials defined in parameters @username and @password.

@enable_SSL – Flag used to specify whether database mail use SSL or not with default value 0.

@account_id – It will return the account id of new database mail account.

Now we will create a database mail account using following code:

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = ‘DB_Email_Account’,

@display_name = ‘SQL Database Email Account’,

@email_address = ‘kapil190588@gmail.com’,

@replyto_address = ‘kapil_k@outlook.com’,

@mailserver_name = ‘smtp.gmail.com’,

@port=587,

@enable_ssl=0

After executing this code, a database mail accounts gets created.  Now we need to add this mail account to a database profile which we will see in our later blogs.

That’s all for the day folks.

Posted in Development, Stored Procedure

Sysmail_help_profile_sp – SQL Server Database Mail Stored Procedure – Part 4

Hi friends, today we will learn how to get information about database mail profile using stored procedure sysmail_help_profile_sp.

Sysmail_help_profile_sp stored procedure provides information about the database mail profiles. This stored procedure is stored in msdb database and owned by dbo schema. The procedure must be executed with a three part name if current database is not msdb.

Syntax:

sysmail_help_profile_sp  [   [ @profile_id = ] profile_id | [ @profile_name = ] ‘profile_name’ ]

Arguments:

@profile_id  is the id of database mail profile with default value NULL.

@profile_name is the name of the database mail profile.

When profile_id or profile_name is specified, sysmail_help_profile_sp stored procedure return information about that profile. If a profile_id or profile_name is not specified it returns information about all profiles in current SQL Server instance.

In our previous blog we created a database mail profile with name ‘Database Test Mail’. Now we will pass that profile name in the parameter to retrieve information about that profile.

EXEC msdb.dbo.sysmail_help_profile_sp @profile_name = ‘Database Test Mail’

sysmail_help_profile

Here, in result set we get information of that database mail profile.

Profile_id – Profile id of database mail profile.

Name – Name of database mail profile.

Description – Description about database mail profile.

That’s all folks for the day. In next blog we will see how to update the information about database mail profile. Have a nice day 🙂