Post reply

using sp_send_dbmail

  • September 25, 2011 at 8:26 pm

    #244399

    Hello,

    we have a database "user1" in one of the user database(dbadver) and "user1" needs to use "msdb.sp_send_dbmail" for their application, but I am not sure how to assign user1 to execute this system stored procedure (sp_send_dbmail). Can some body please let me know the steps how to assign such privilege.

    Thanks

    Aj

  • September 25, 2011 at 8:31 pm

    #1386267

    in the msdb database, you'll need to add the user, adn then make him a member of the [DatabaseMailUserRole] role.

    USE [msdb]

    GO

    CREATE USER [user1] for login [user1]

    EXEC sp_addrolemember 'DatabaseMailUserRole','user1'

    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!

  • September 25, 2011 at 9:53 pm

    #1386272

    I hate giving users any privs. We've got it pretty much locked down at work so that users have no privs other than PUBLIC and "execute". All of our stored procedures are owned by "SA". If you simply add the command to "execute as owner" and grant the user the privs to execute the proc (actually, all of our users are members of the database role we call "Executor" which is allowed to run all stored procedures in the DB), they can send mail by using the proc without elevating their privs nor can they send mail without using the proc.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

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