Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
GMANAFIP
Starting Member
13 Posts |
 Posted - 2008-08-15 : 11:09:40
|
| I imported a SQL Server 2000 DTS package to 2K5 as a legacy DTS under SSIS. Everything works fine but the mail component. I configured the mail to send mail but I keep getting an error: "Execution permission was on the denied object 'sp_send_dbmail', database 'msdb', schema 'dbo'. I use the DTS under SQL Server 2000 with 'sa' permission and I thought the permission would carry over since this is a upgrade. What user should I add to msdb database to execute the sp_send_dbmail store procedure? Any help you could give would be great. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-15 : 11:29:25
|
| User must be member of DatabaseMailUserRole in MSDB database. |
 |
|
|
GMANAFIP
Starting Member
13 Posts |
Posted - 2008-08-15 : 12:35:53
|
quote:
Originally posted by sodeep
User must be member of DatabaseMailUserRole in MSDB database.
How do I verify what user the DTS is running under? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-15 : 12:40:04
|
| Complete explanation is here:http://msdn.microsoft.com/en-us/library/ms187540.aspx |
|
|
|
GMANAFIP
Starting Member
13 Posts |
Posted - 2008-08-18 : 11:12:41
|
quote:
Originally posted by sodeep
Complete explanation is here:http://msdn.microsoft.com/en-us/library/ms187540.aspx
I'm still having problems getting it to work. I added the user and gave it execute permission and it still did not work - same message. I can run the script below in a query window on the server but when I run the 2000 DTS package in 2005 I get an error. Script on the server:EXEC msdb.dbo.sp_send_dbmail @profile_name= N'Administrator', @recipients=N'jdoes@yahoo.com', @body=N'The test mail message.' ;Any ideas? Thanks |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-18 : 13:01:26
|
quote:
Originally posted by GMANAFIP
quote:
Originally posted by sodeep
Complete explanation is here:http://msdn.microsoft.com/en-us/library/ms187540.aspx
I'm still having problems getting it to work. I added the user and gave it execute permission and it still did not work - same message. I can run the script below in a query window on the server but when I run the 2000 DTS package in 2005 I get an error. Script on the server:EXEC msdb.dbo.sp_send_dbmail @profile_name= N'Administrator', @recipients=N'jdoes@yahoo.com', @body=N'The test mail message.' ;Any ideas? Thanks
Why yahoo? Not sure about this. |
|
|
|
GMANAFIP
Starting Member
13 Posts |
Posted - 2008-08-19 : 09:46:31
|
quote:
Originally posted by sodeep
quote:
Originally posted by GMANAFIP
quote:
Originally posted by sodeep
Complete explanation is here:http://msdn.microsoft.com/en-us/library/ms187540.aspx
I'm still having problems getting it to work. I added the user and gave it execute permission and it still did not work - same message. I can run the script below in a query window on the server but when I run the 2000 DTS package in 2005 I get an error. Script on the server:EXEC msdb.dbo.sp_send_dbmail @profile_name= N'Administrator', @recipients=N'jdoes@yahoo.com', @body=N'The test mail message.' ;Any ideas? Thanks
Why yahoo? Not sure about this.
I just used Yahoo.com as an example of an email. Any ideas on what could be the problem? |
|
|
|
|
|
|
error when using legacy DTS inside 2005