Skip to content

Latest commit

 

History

History
83 lines (56 loc) · 3.73 KB

sysmail-delete-principalprofile-sp-transact-sql.md

File metadata and controls

83 lines (56 loc) · 3.73 KB
Raw
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sysmail_delete_principalprofile_sp (Transact-SQL)
Removes permission for a database user or role to use a public or private Database Mail profile.
markingmyname
maghan
randolphwest
11/02/2023
sql
system-objects
reference
sysmail_delete_principalprofile_sp_TSQL
sysmail_delete_principalprofile_sp
sysmail_delete_principalprofile_sp
TSQL

sysmail_delete_principalprofile_sp (Transact-SQL)

[!INCLUDE SQL Server]

Removes permission for a database user or role to use a public or private Database Mail profile.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sysmail_delete_principalprofile_sp { [ @principal_id = ] principal_id | [ @principal_name = ] 'principal_name' } ,
    { [ @profile_id = ] profile_id | [ @profile_name = ] 'profile_name' }
[ ; ]

Arguments

[ @principal_id = ] principal_id

The ID of the database user or role in the msdb database for the association to delete. @principal_id is int, with a default of NULL. To make a public profile into a private profile, provide the principal ID 0 or the principal name public. Either @principal_id or @principal_name must be specified.

[ @principal_name = ] 'principal_name'

The name of the database user or role in the msdb database for the association to delete. @principal_name is sysname, with a default of NULL. To make a public profile into a private profile, provide the principal ID 0 or the principal name public. Either @principal_id or @principal_name must be specified.

[ @profile_id = ] profile_id

The ID of the profile for the association to delete. @profile_id is int, with a default of NULL. Either @profile_id or @profile_name must be specified.

[ @profile_name = ] 'profile_name'

The name of the profile for the association to delete. @profile_name is sysname, with a default of NULL. Either @profile_id or @profile_name must be specified.

Return code values

0 (success) or 1 (failure).

Remarks

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 causes 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 isn't msdb.

Permissions

[!INCLUDE msdb-execute-permissions]

Examples

The following example shows deleting the association between the profile AdventureWorks Administrator and the login ApplicationUser in the msdb database.

EXEC msdb.dbo.sysmail_delete_principalprofile_sp
    @principal_name = 'ApplicationUser',
    @profile_name = 'AdventureWorks Administrator';

Related content