August 19, 2008 at 8:52 am
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 used the DTS under SQL Server 2000 with 'sa' permission and I thought the permission would carry over since this is a upgrade.
I made the user a member of DatabaseMailUserRole in MSDB database and gave it execute permission but 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'myemail@aol.com',
@body=N'The test mail message.' ;
Any ideas?
August 19, 2008 at 9:01 am
Are you connecting to SQL 2k with the same credentials? eg sa?
can you select * From sysjobs on the smdb from within ssis?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 19, 2008 at 9:27 am
Are you connecting to SQL 2k with the same credentials? eg sa?
YES
can you select * From sysjobs on the msdb from within ssis?
NO
What does that mean?
August 19, 2008 at 9:29 am
It means you do not have permissions. What was the error? (Detailed please)
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 19, 2008 at 9:58 am
The error was Select permission was denied the object 'sysjob', database 'msdb', schema 'dbo'.
What do I do now?
August 19, 2008 at 10:03 am
Either look in BOL on how to change select permissions or to to the security section on this site and ask there as it is not now a ssis issue.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply