June 24, 2013 at 6:31 am
Hi all
I have stored procedure DoTransactions that is used to create a number of transactions in a database where the front-end application would usually have done the data inserts/updates. The SP on completion (whether failure or success) then passes a identifying ID to another stored procedure SendEmail which in turn sends an e-mail to a user confirming that the process had completed with whatever results.
DoTransactions must run as a specific SQL user. The transactions it creates all confirm whether this user is created in the application tables and has sufficient rights. When logged into SSMS as this user and manually executing DoTransactions, SendEmail sends e-mails without any problem. DoTransactions however has to be scheduled to run hourly and I have set up a SQL Agent job for this. The job executes as the specific user and I know that is working correctly or else all the transactions would fail. It also successfully executes stored procedure SendEmail however in SendEmail there is a check that always fails (below) although the job step completes correctly:
IF EXISTS (SELECT * FROM master.dbo.sysdatabases s WHERE s.name = 'msdb' )
If logged into SSMS as the specific SQL user, I can execute the above and get the expected result although from the job it seemingly doesn't. Is it possible that the user executing SendEmail differs from the one executing the job (and therefore DoTransactions)?
Snippet where DoTransactions calls SendEmail:
EXEC SendEmail @logid
Snippet where SendEmail always fails when executed from DoTransaction via SQL Agent. It always ends up at 'Database "msdb" does NOT exist':
IF EXISTS (SELECT *
FROM master.dbo.sysdatabases s
WHERE s.name = 'msdb' )
BEGIN
PRINT 'Database "msdb" does exist.'
IF EXISTS (SELECT *
FROM msdb.sys.objects so
JOIN msdb.sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name = 'sp_send_dbmail'
AND sc.name = 'dbo' )
BEGIN
IF EXISTS (SELECT *
FROM msdb.dbo.sysmail_profile )
BEGIN
DECLARE @MailSubjectNVARCHAR(MAX),
@ProfileNameNVARCHAR(128)
SELECT @ProfileName = s.name
FROM msdb.dbo.sysmail_profile s
IF NOT EXISTS (SELECT *
FROM msdb.dbo.sysmail_principalprofile p
WHERE p.principal_sid = 0x00 )
BEGIN
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@principal_name= 'public',
@profile_name= @ProfileName,
@is_default= 1 ;
END
EXEC MSDB.dbo.sp_send_dbmail
@profile_name= @ProfileName,
@recipients= 'test@test.test',
@subject= 'Automated Notification: DoTransactions',
@body= @MailOutBody,
@body_format= 'TEXT',
@importance= 'NORMAL'
END
END
ELSE
BEGIN
PRINT 'Procedure "dbo.sp_send_dbmail" does NOT exist.'
END
END
ELSE
BEGIN
PRINT 'Database "msdb" does NOT exist.'
END
I appreciate that I could remove most of the checks above so that it simply sends the e-mail or even copy the above into DoTransactions so that I know the correct user is running this piece of SQL but I would like to understand better why this is happening in SQL Agent and not SSMS.
June 24, 2013 at 9:18 am
I think your starting point is to look at which account the job step is running under.
If that login does not have the VIEW ANY DATABASE permission, then a query against the sys.databases table will only return rows for "master" and "tempdb".
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 25, 2013 at 1:18 am
Matthew Darwin (6/24/2013)
I think your starting point is to look at which account the job step is running under.If that login does not have the VIEW ANY DATABASE permission, then a query against the sys.databases table will only return rows for "master" and "tempdb".
There is only one job step and this is run as the SQL user that has all server roles assigned to it. So basically this SQL user has the same rights as the sa user. Also when logged in as the specific SQL user in SSMS, you can successfully run the DoTransactions SP which in turn successfully calls and sends email via the SendEmails SP so it doesn't seem to be something wrong with the account but more how the 2nd SP gets called from within the job which is the only time the SendEmails SP check fails.
I have double checked the roles and am able to query sys.databases and get master, tempdb, model, msdb and all the user created DB's back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply