August 20, 2008 at 9:42 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.' ;
I'm told I need different permission. I tried running the package with both a domain admin account and a SQL Server system adminstrator account and neither works. Do I need a service account to run the Database Mail in SQL Server 2005? How do I configure the permissions?
Thanks
August 20, 2008 at 9:52 am
i would think SA has the rights, so i'd look to make sure the step isn't running as some other user (who doesn't have permissions). what do you get if you try to exec sp_send_dbmail in a session logged in as SA? if it works there, then it's likely a different user is trying to execute it.
August 20, 2008 at 11:50 am
How do I find out what user is being used when the package is running? I thought it was the onwer of the package a domain admin account but I think it i guess it is not. Any ideas?
August 20, 2008 at 2:30 pm
i'm guessing it worked when you EXECed it as SA in a new window?
to find out who's executing the proc, you could run a profiler trace filtered so that it only displays calls to sp_send_dbmail.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply