Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Removes permission for a database user or role to use a public or private Database Mail profile.
Applies to: SQL Server (SQL Server 2008 through current version).
To make a public profile into a private profile, provide 'public' for the principal name or 0 for the principal id.
Use caution when removing permissions for the default private profile for a user or the default public profile. When no default profile is available, sp_send_dbmail requires the name of a profile as an argument. Therefore, removing a default profile may cause calls to sp_send_dbmail to fail. For more information, see sp_send_dbmail (Transact-SQL).
The stored procedure sysmail_delete_principalprofile_sp is in the msdb database and is owned by the dbo schema. The procedure must be executed with a three-part name if the current database is not msdb.
The following example shows deleting the association between the profile AdventureWorks Administrator and the login ApplicationUser in the msdb database.
EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp @principal_name = 'ApplicationUser', @profile_name = 'AdventureWorks Administrator' ;