Sql-server – Sending mail using DatabaseMail in SQL Server 2008

sql-server-2008

I have a profile called GPMail, and previously there was an account called comments@guitarplayer.com, which was working as expected.

Now I want one more account with the same site, so instead of creating new profile I created new account called password@guitarplayer.com inside the same profile. But now when I try to send mail for password, I do not get the option for the account in the stored procedure — sp_send_dbmail, so whenever I send the mail, the from field has the comments@guitarplayer.com address instead of password@guitarplayer.com.

Is there anyway that I can specify the account name too with Profile name while sending mails?

Best Answer

Depending on the method you use for sending mail: Yes

If you use sp_send_dbmail (which is what I use), then yes, you can set the FROM address in the call to send mail.

See docs: http://msdn.microsoft.com/en-us/library/ms190307.aspx

@from_address is what you want, I believe

If you want to actually log in to the mail server as a diff user, then you need to mod the profile or create a new one. The auth to the mail server is configured in the profile.

Related Solutions

Sql-server – Save Transaction: Is the name local to the stored procedure or not

No they aren't local to a stored procedure.

If you run the following in a database with simple recovery model

checkpoint
begin tran;
exec dbo.foo_test_tran_outer;
commit tran;
select * from dbo.footest;

select Operation,
       Context,
       [Savepoint Name],
       [Transaction Name] ,
       Description 
from sys.fn_dblog(NULL,NULL)

It gives these results

+--------------------+----------+-------------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|     Operation      | Context  |  Savepoint Name   | Transaction Name |                                                                                               Description                                                                                                |
+--------------------+----------+-------------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOP_BEGIN_CKPT     | LCX_NULL | NULL              | NULL             |                                                                                                                                                                                                          |
| LOP_END_CKPT       | LCX_NULL | NULL              | NULL             | log_minRecoveryLsn 00000112:00001ce6:008e;log_replbeginlsn 00000000:00000000:0000;log_replnextlsn 00000000:00000000:0000;log_distbackuplsn 00000000:00000000:0000;log_distlastlsn 00000000:00000000:0000 |
| LOP_BEGIN_XACT     | LCX_NULL | NULL              | user_transaction | user_transaction;0x010500000000000515000000007dfcff481d5bed8a729370ee030000                                                                                                                              |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             |                                                                                                                                                                                                          |
| LOP_MARK_SAVEPOINT | LCX_NULL | the_constant_name | NULL             |                                                                                                                                                                                                          |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             |                                                                                                                                                                                                          |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             |                                                                                                                                                                                                          |
| LOP_MARK_SAVEPOINT | LCX_NULL | the_constant_name | NULL             |                                                                                                                                                                                                          |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             |                                                                                                                                                                                                          |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             | COMPENSATION                                                                                                                                                                                             |
| LOP_LOCK_XACT      | LCX_NULL | NULL              | NULL             | COMPENSATION                                                                                                                                                                                             |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             | COMPENSATION                                                                                                                                                                                             |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             | COMPENSATION                                                                                                                                                                                             |
| LOP_LOCK_XACT      | LCX_NULL | NULL              | NULL             | COMPENSATION                                                                                                                                                                                             |
| LOP_COMMIT_XACT    | LCX_NULL | NULL              | NULL             |                                                                                                                                                                                                          |
+--------------------+----------+-------------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The [Savepoint Name] recorded in the transaction log does not contain any unique identifier related to stored procedure.

If you remove the rollback tran the_constant_name; from foo_test_tran_inner then the result of the final select changes to Inner, before savepoint showing that the rollback tran the_constant_name; executed from foo_test_tran_outer just rolls back to the most recent (unrolled back) savepoint of that name and takes no account of stored procedure nesting.

Sql-server – Restricting mail sending from SQL Server 2008

You just need to ensure the profile isn't public when you assign permissions and access to it. The stored procedure in the msdb database is msdb.dbo.sysmail_add_principalprofile_sp, and this stored proc is what grants permissions for an msdb database role or user.

Excerpt taken from BOL on the subject:

To make a profile public, specify a @principal_id of 0 or a @principal_name of public. A public profile is available to all users in the msdb database, though users must also be a member of DatabaseMailUserRole to execute sp_send_dbmail.

BOL reference on Creating a Database Mail Private Profile.

Best Answer

Related Solutions

Sql-server – Save Transaction: Is the name local to the stored procedure or not

Sql-server – Restricting mail sending from SQL Server 2008

Related Question