What SQL Jobs were Running?

With performance issues with SQL Jobs sometimes I want to know what other jobs were running at the same time. With these two queries I get that information. Query 1 lets me get the exact name of the job and what time it ran. You may ask how come I did not know the exact name of the job. We have lots of jobs and that is all the developer gave me. :-)


USE MSDB
Go

SELECT DISTINCT
                         sysjobs.name, sysjobhistory.instance_id, sysjobhistory.job_id, sysjobhistory.step_id, sysjobhistory.step_name, sysjobhistory.sql_message_id,
                         sysjobhistory.sql_severity, sysjobhistory.message, sysjobhistory.run_status, sysjobhistory.run_date, sysjobhistory.run_time, sysjobhistory.run_duration,
                         sysjobhistory.retries_attempted, sysjobhistory.server
FROM            sysjobhistory INNER JOIN
                         sysjobs ON sysjobhistory.job_id = sysjobs.job_id
WHERE        (sysjobhistory.run_date = 20150616) AND (sysjobs.name LIKE N'BI Pop%')



USE MSDB
Go

SELECT DISTINCT
                         sysjobs.name, sysjobhistory.instance_id, sysjobhistory.step_id, sysjobhistory.step_name, sysjobhistory.message, sysjobhistory.run_status,
                         sysjobhistory.run_date, sysjobhistory.run_time, sysjobhistory.run_duration, sysjobhistory.server
FROM            sysjobhistory INNER JOIN
                         sysjobs ON sysjobhistory.job_id = sysjobs.job_id
WHERE        (sysjobhistory.run_date = 20151123) AND (sysjobhistory.run_time >= 190000) AND (sysjobhistory.run_time <= 193000)
ORDER BY sysjobhistory.run_time DESC

orphaned users

To detect orphaned users, execute the following Transact-SQL statements:

USE <database_name>

GO
sp_change_users_login @Action='Report'
GO

The following command relinks the server login account specified by <login_name> with the database user specified by <database_user>.

USE <database_name>;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>';
GO

To fix all Orphaned users in a DB:


BEGIN
 DECLARE @username varchar(25)
 DECLARE fixusers CURSOR
 FOR
 SELECT UserName = name FROM sysusers
  WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
   and suser_sname(sid) is null
    ORDER BY name
 OPEN fixusers
 FETCH NEXT FROM fixusers
 INTO @username
 WHILE @@FETCH_STATUS = 0
 BEGIN
  EXEC sp_change_users_login 'update_one', @username, @username
  FETCH NEXT FROM fixusers
  INTO @username
 END
 CLOSE fixusers
 DEALLOCATE fixusers
END