none
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

    Question

  • When a user logs into our domain, a script is executed which records the asset details which they are logging into.

    The user inserts a record into a table, which fires a trigger. The trigger executes different things depending on the configuration of the workstation. At the moment with support on Windows XP SP2 coming to an end, we are capturing the users which have SP2 installed. When a user who has SP2 enters a record into the assets table, the trigger logs a job with our IT Helpdesk. The execution of this SP triggers another trigger which is supposed to email the user back with their ticket information.

    Normally, a user logs a job via the intranet, and the execution is handled under a SQL account via an ASP.NET page. However, in this case, the user is trying to log a job under their own security context. I have given the DOMAIN\DOMAIN USERS group execute permissions on this stored procedure, so they can now log their jobs under their NT AUTHENTICATION. However they encounter the execute error on sp_send_dbmail as the title of the thread suggests.

    My question is:
    Is it wise to grant DOMAIN\DOMAIN USERS access to sp_send_dbmail so that in this case the trigger can send the email back to them, or is this bad practice?

    I wondered if i could execute the stored procedure to log the IT Job under another user which has adequate permissions, but i presume the user would need permissions to impersonate this user in any case.

    Thanks in advance,

    Dan.

    Edit: I should probably add that the Asset and Helpdesk aspects of this question are two seperate databases on the same server.

    • Edited by Danny Thompson Tuesday, June 08, 2010 9:36 AM additional information
    Tuesday, June 08, 2010 9:32 AM

Answers

  • Is it wise to grant DOMAIN\DOMAIN USERS access to sp_send_dbmail so that in this case the trigger can send the email back to them, or is this bad practice?

    I wondered if i could execute the stored procedure to log the IT Job under another user which has adequate permissions, but i presume the user would need permissions to impersonate this user in any case.


    You can add the domain group to the msdb role as Uri suggested if you don't mind granting all domain users permissions to execute sp_send_dbmail. But if you want prevent users from sending ad-hoc mail yet allow mail to be sent from your trigger code, you have a couple of options.

    One method is to enable cross-database ownership chaining ('db chaining' database option) in your user database.  Users will then not need permissions on indirectly referenced objects (like sp_send_dbmail in your trigger) as long the databases and objects involved have the same owner.  In your case, this means the database must be owned by 'sa' and the objects/schema owned by 'dbo'.  But be aware of the security implications.  You should enable 'db chaining' in an sa-owned database only when dbo-owned object creation is restricted to sysadmin role members.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Danny Thompson Wednesday, June 09, 2010 10:46 AM
    Tuesday, June 08, 2010 12:28 PM
  • The msdb system database is owned by 'sa' so your user database will also need to be owned by 'sa' for the ownership chain to remain unbroken.  You don't need to use the 'sa' login for purposes other than authorization.  Below is a sample script need to implement this.

    ALTER AUTHORIZATION ON DATABASE::MyDatabase TO sa;
    ALTER DATABASE MyDatabase SET DB_CHAINING ON;


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Danny Thompson Wednesday, June 09, 2010 10:46 AM
    Tuesday, June 08, 2010 10:18 PM

All replies

  • Danny

    BOL says "Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message"

    • Marked as answer by Danny Thompson Wednesday, June 09, 2010 10:47 AM
    • Unmarked as answer by Danny Thompson Wednesday, June 09, 2010 10:47 AM
    Tuesday, June 08, 2010 10:49 AM
  • Is it wise to grant DOMAIN\DOMAIN USERS access to sp_send_dbmail so that in this case the trigger can send the email back to them, or is this bad practice?

    I wondered if i could execute the stored procedure to log the IT Job under another user which has adequate permissions, but i presume the user would need permissions to impersonate this user in any case.


    You can add the domain group to the msdb role as Uri suggested if you don't mind granting all domain users permissions to execute sp_send_dbmail. But if you want prevent users from sending ad-hoc mail yet allow mail to be sent from your trigger code, you have a couple of options.

    One method is to enable cross-database ownership chaining ('db chaining' database option) in your user database.  Users will then not need permissions on indirectly referenced objects (like sp_send_dbmail in your trigger) as long the databases and objects involved have the same owner.  In your case, this means the database must be owned by 'sa' and the objects/schema owned by 'dbo'.  But be aware of the security implications.  You should enable 'db chaining' in an sa-owned database only when dbo-owned object creation is restricted to sysadmin role members.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Danny Thompson Wednesday, June 09, 2010 10:46 AM
    Tuesday, June 08, 2010 12:28 PM
  • Thanks Uri & Dan.

    Dan,

    I'll have to take a look further into DB ownership chaning. That sounds like an option only our sa account is not used - each database has its own sql user owner. I presume we'd just create a master 'database owner' sql user account which would own all databases.

    I'll mark yours as the answer Dan, as i think this is the type of solution i require, only i don't know enough about db chaining to implement at this stage.

    Thank you both for the advice.

    Tuesday, June 08, 2010 1:11 PM
  • The msdb system database is owned by 'sa' so your user database will also need to be owned by 'sa' for the ownership chain to remain unbroken.  You don't need to use the 'sa' login for purposes other than authorization.  Below is a sample script need to implement this.

    ALTER AUTHORIZATION ON DATABASE::MyDatabase TO sa;
    ALTER DATABASE MyDatabase SET DB_CHAINING ON;


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Danny Thompson Wednesday, June 09, 2010 10:46 AM
    Tuesday, June 08, 2010 10:18 PM
  • Thank you both
    Wednesday, June 09, 2010 10:47 AM