SQL Server – Mail – Granting users access to mail profile

Background

Post setting up a critical SQL Server Agent Job, wanted to setup email alert upon completion; good or bad let someone know.

 

Error

My first attempt led here….

Image

Textual

Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 112 [Batch Start Line 8]
No global profile is configured. Specify a profile name in the @profile_name parameter.


Remediation

Public Profile

Grant all MSDB users permission to database profile.

GUI

Outline

  1. Click on Object Explorer
  2. Choose the the Sql Instance
  3. Navigate to Management \ Database Mail
  4. Right click on Management \ Database Mail and from the drop-down menu, choose Properties
  5. In the “Manage Profile Security” window
    • Keep focused on the “Public Profiles” tab
    • Place a checkmark on all profiles that you will like to make public
  6. In the “Select Configuration Task” window choose “Manage Profile Security”
  7. In the “Manage Profile Security” window, place a checkmark on all profiles that you will like to make public
  8. In the “Complete the Wizard” window, if there are profiles that were previously not public, they are displayed and we are given an opportunity to effect the change

Images

Object Explorer

Database Mail Configuration Wizard – Select Configuration Task

Database Mail Configuration Wizard – Manage Profile Security – Public Profiles – Viewing

We create public profiles by checking the public checkbox on the profile.

Database Mail Configuration Wizard – Manage Profile Security – Public Profiles – Configuring

SQL

SQL – Syntax


EXEC msdb.dbo.sysmail_add_principalprofile_sp
        @principal_name=N'guest'
      , @profile_name=[profile_name]
      , @is_default=0

SQL – Sample


EXEC msdb.dbo.sysmail_add_principalprofile_sp
        @principal_name=N'guest'
      , @profile_name=N'SQL Server Notification Service'
      , @is_default=0

 

Private Profile

Grant specific MSDB user permission to database profile.

Outline

  1. Click on Object Explorer
  2. Choose the the Sql Instance
  3. Navigate to Management \ Database Mail
  4. Right click on Management \ Database Mail and from the drop-down menu, choose Properties
  5. In the “Select Configuration Task” window choose “Manage Profile Security”
  6. In the “Manage Profile Security” window
    • Access the “Private Profiles” tab
    • Place a checkmark on all profiles that you will like to make public
  7. In the “Complete the Wizard” window, if there are profiles that were previously not public, they are displayed and we are given an opportunity to effect the change

 

GUI

Images

Object Explorer

Database Mail Configuration Wizard – Manage Profile Security – Private Profiles – Viewing

We create private profiles by accessing the “Private Profiles” tab, choosing the msdb user that we will like augmented, and checking each private profiles we will like to grant access to.

SQL – Syntax


EXEC msdb.dbo.sysmail_add_principalprofile_sp
        @principal_name=[account]
      , @profile_name=[profile_name]
      , @is_default=0

SQL – Sample


EXEC msdb.dbo.sysmail_add_principalprofile_sp 
          @principal_name=N'NT SERVICE\ReportServer'
        , @profile_name=N'SQL Server Notification Service'
        , @is_default=0

Review Mail Profile User Security Permisions

Issue SQL to check the msdb.dbo.sysmail_principalprofile table.

This table binds each principal to registered profile.


select 
		  [profile]
			= tblSMP.[name]

                , [profileID]
			= tblSMPP.profile_id

		, [principalID]	
			= tblSMPP.principal_sid

		, [principal] 
			= tblSDP.[name]
			
		, [isDefault]
			 = tblSMPP.is_default

		, [modifiedBy] 
			= tblSMPP.last_mod_user

		, [modifiedOn] 
			= convert
				(
					  varchar(30)
					, tblSMPP.last_mod_datetime
					, 100
				)

from  [msdb].[dbo].[sysmail_profile] tblSMP

inner join [msdb].[dbo].[sysmail_principalprofile] tblSMPP

	on tblSMP.[profile_id] = tblSMPP.profile_id

inner join sys.database_principals tblSDP

	on tblSMPP.principal_sid = tblSDP.[sid]

Image

 

References

  1. Microsoft
    • Database Mail Stored Procedures ( Transact-SQL )
    • Add Principal Profile
      • sysmail-add-principalprofile-sp ( Transact-SQL )
    • Update Principal Profile
      • sysmail_update_principalprofile_sp ( Transact-SQL )
  2.  SQLServerCentral.com
    • Home»SQL Server 2008»SQL Server 2008 – General»Converting User SID Binary to String TSQL
      Link

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

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