Post reply

How do i grant permission to msdb.dbo.sp_send_dbmail ?

  • March 30, 2009 at 12:42 pm

    #198616

    I have my custom sp in my testDB database which is using dbo.sp_send_dbmail system sp from msdb. I have many users to use my custom stored procedure. But i cannot give permissions to all the users. Instead i created one login 'Admin_User' which has the following permissions granted:

    1. Added user 'Admin_User' to msdb database with role 'DatabaseMailUserRole'.

    2. Default security profile 'TEST_EMAIL' is added to user 'Admin_User'

    Now i have a user with name 'test' in testDB database have to access my custom sp to send email. but this user doesn't have any permission in msdb database, since this user is not in msdb database.

    So, when i execute the sp, i am getting following error:

    Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1

    EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

    Sometimes, i used to get this error as well:

    Msg 15517, Level 16, State 1, Procedure sp_send_email, Line 17

    Cannot execute as the database principal because the principal "Admin_User" does not exist, this type of principal cannot be impersonated, or you do not have permission.

    I have checked this, but didnt get any answer from this article: http://msdn.microsoft.com/en-us/library/ms188304.aspx

    so, how do i impersonate 'Admin_User' on msdb so that i can execute my custom sp to send email through 'test' user. I cant give any permission to test user in msdb. (Because there will be lot of users like this)

    I tried to get the user_name who is executing the sp using SELECT user_name(). it returned the user name 'Admin_User'.

    Please help me on this. Thanks in Advance.

    CODE:

    CREATE PROCEDURE [dbo].[sp_email]

    (

    @recipients VARCHAR(MAX),

    @subject VARCHAR(MAX),

    @body VARCHAR(MAX)

    )

    WITH EXECUTE AS 'Admin_User'

    AS

    BEGIN

    IF (@body IS NOT NULL AND @subject IS NOT NULL AND @recipients IS NOT NULL)

    begin

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'TEST_PROFILE',

    @recipients = @recipients,

    @subject = @subject,

    @body = @body,

    @body_format = 'TEXT';

    end

    END

  • August 29, 2011 at 3:28 pm

    #1374753

    Did you get this resolved? If you don't mind, would you please post what you did to fix it?

  • December 27, 2011 at 2:34 pm

    #1425875

    If anyone is watching this post and has a solution I would like to know what it is.

    Thanks,

    Howard

  • December 27, 2011 at 4:51 pm

    #1425903

    the solutions is fairly simple, but it varies a bit based on your specific setup. it all boils down to the group or login needs to be part of the built inDatabaseMailUserRole in msdb

    lets assume this scenario;

    a Windows Login [mydomain\Clarkkent or SQL Login [ClarkKent] is being used to get to the PRODUCTION database, and you want to let that user use the procedure that uses dbmail. that login has a matching USER in the PRODUCTION database we talked about.

    you would go to the msdb database, and add that user to the msdb database also.

    USE msdb;

    --add our user

    CREATE USER ClarkKent FOR LOGIN ClarkKent;

    --give this user rights to use dbmail

    exec sp_addrolemember 'DatabaseMailUserRole', 'ClarkKent'

    now if we know ClarkKent is getting his authorization from a windows group, then you add that windows group as a user, and add that group to the same role;

    USE msdb;

    --add our user via a group we know he is in

    CREATE USER 'mydomain\BusinessGroup' FOR LOGIN 'mydomain\BusinessGroup';

    --give this GROUP rights to use dbmail

    exec sp_addrolemember 'DatabaseMailUserRole', 'mydomain\BusinessGroup'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 27, 2011 at 6:24 pm

    #1425907

    Lowell:

    Thank you for the detailed instructions. I have three questions if you have the time to answer them but if your time is limited an answer to question #1 would be greatly appreciated.

    Question #1:

    I am using SQL authentication and not Windows authentication. How would this change your instructions?

    Question #2:

    I have dbmail working but I noticed that the profile that I am using in the procedure is not public and not the default profile. Could the solution be as simple as changing this profile to Public and Default profile?

    Question #3:

    In attempting to resolve the issue I used SSMS and in Database > msdb > Security > Roles > Database Roles > DatabaseMailUserRole > Properties > General > Schemas owned by this role > I checked db_owner. Obviously this did not solve my problem. However, now I cannot uncheck db_owner. It is grayed out. Do you know how I can uncheck db_owner?

    Thanks

    Howard

  • December 27, 2011 at 7:09 pm

    #1425911

    hmbtx (12/27/2011)

    Lowell:

    Thank you for the detailed instructions. I have three questions if you have the time to answer them but if your time is limited an answer to question #1 would be greatly appreciated.

    Question #1:

    I am using SQL authentication and not Windows authentication. How would this change your instructions?

    Same solution. add the SQL login as a user to msdb, and then add the user to the built in role i identified. my first code example was a sql login, but the solution is the same.

    Question #2:

    I have dbmail working but I noticed that the profile that I am using in the procedure is not public and not the default profile. Could the solution be as simple as changing this profile to Public and Default profile?

    making one of your profiles a default really only has the affect of allowing you to eliminate one of the parameters for the sp_send_dbmail call; while i do have a default profile set up, all my mail calls explicitly identify the profile name, because you just don't know that someone might drop the profile or change the default sometime in the future. so my answer is good idea, but don't code around an assumed default profile.

    Question #3:

    In attempting to resolve the issue I used SSMS and in Database > msdb > Security > Roles > Database Roles > DatabaseMailUserRole > Properties > General > Schemas owned by this role > I checked db_owner. Obviously this did not solve my problem. However, now I cannot uncheck db_owner. It is grayed out. Do you know how I can uncheck db_owner?

    Thanks

    Howard

    hehe, that's a GUI bug... i just duplicated your steps and see the issue :-D.

    cannot undo it via the GUI, so script it instead:

    USE msdb;

    sp_droprolemember 'db_owner','DatabaseMailUserRole'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 28, 2011 at 10:37 am

    #1426184

    Lowell:

    Thanks again for your detailed response to my questions.

    #1:

    I executed the following script but it did not drop db_owner from DatabaseMailUserRole.

    Do I need to stop and start SQL Server for this action to take place?

    USE msdb;

    sp_droprolemember 'db_owner','DatabaseMailUserRole'

    #2:

    I executed the following script (in my test I substituted ClarkKent with a real user name):

    USE msdb;

    --add our user

    CREATE USER ClarkKent FOR LOGIN ClarkKent;

    --give this user rights to use dbmail

    exec sp_addrolemember 'DatabaseMailUserRole', 'ClarkKent'

    In my stored procedure I added with execute as 'ClarkKent'

    I made the profile public and default profile.

    The script still fails when run as a job.

    Can you tell me if I have missed a step or two?

    Thanks

    Howard

  • December 30, 2011 at 1:50 pm

    #1427123

    Add the user to DatabaseMailUserRole in msdb

  • January 5, 2012 at 8:04 am

    #1429042

    Lowell:

    I have more information on

    USE msdb;

    sp_droprolemember 'db_owner','DatabaseMailUserRole'

    not working and possibly a bug as we previously discussed on this posting.

    I sent the following question to microsoft:

    Using SSMS in Databases > System Databases > msdb > Security > Roles > Database Roles > DatabaseMailUserRole > Properties > General > Schemas owned by this role > I checked db_owner.

    However, now I cannot uncheck db_owner. It is grayed out.

    I then used the following T-SQL commands:

    use msdb;

    sp_droprolemember 'db_owner', 'DatabaseMailUserRole;

    and although the message returned was 'Command(s) completed successfully' the role was not dropped.

    Do you know how I can uncheck db_owner?

    Microsoft's response is as follows:

    Posted by Microsoft on 1/5/2012 at 2:54 AM

    This is not a real issue. In the repro steps provided by the customer, he is making role the owner of db_owner schema. Ownership of a schema can be changed by "ALTER AUTHORIZATION" T-SQL not by sp_droprolemember.

    In order to change the ownership of a schema, one will have to open the properties dialog of a user/role which should be made the owner of the schema and then select this schema "db_owner" in the Owned schemas box and click Ok

    What is your opinion of their answer?

    Do you think I should post this as a separate question on this forum?

    Thanks

    Howard

  • March 20, 2013 at 6:23 am

    #1598858

    Thanks. This is the solution.

    4x4 photos

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply