December 16, 2014 at 11:44 am
Hi all,
SQL Server 2008R2 on Windows server 2008.
Last night many scheduled jobs failed with "Cannot open user default database. Login failed." message.
SQLAGENT.EXE runs under SYSTEM account. I tried to google potential fixes, but can't seem to find any.
What may caused it and where do I start looking for how to fix it?
Thanks,
December 16, 2014 at 12:46 pm
rightontarget (12/16/2014)
Hi all,SQL Server 2008R2 on Windows server 2008.
Last night many scheduled jobs failed with "Cannot open user default database. Login failed." message.
SQLAGENT.EXE runs under SYSTEM account. I tried to google potential fixes, but can't seem to find any.
What may caused it and where do I start looking for how to fix it?
Thanks,
First has anything changed? Did someone update the user login or the default database? Make sure the jobs are using the login you think. We had jobs fail when a user left as they owned the jobs. (All jobs now owned by a system account)
December 16, 2014 at 4:27 pm
This query will find logins with a default db that is not online, and show any job(s) that login owns.
select sp.*, j.*
from sys.server_principals sp
left outer join msdb.dbo.sysjobs j on
j.owner_sid = sp.sid
where
sp.default_database_name not in (select d.name from sys.databases d where d.state_desc = 'ONLINE')
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply