Resync Usernames and Logins after Database Move

Use sp_change_users_login to accomplish this. Here is a screen shot from my workstation with one way to do it (the way I do it). Any other methods or suggestions are welcome.

Finding Dependencies with Sysdepends

We are working on a a database "rethinking" project. As we go about the change process we want to make sure that as we "fix stuff" we don't break other "stuff." Here is a quick query I wrote using sysdepends to help ensure that we don't leave anything orphaned during our database revamping:

select b.name as ObjectName,
c.name as DependantObjectName,
c.xtype as DependantObjectType
from sysdepends a
inner join sysobjects b
on a.id = b.id
inner join sysobjects c
on a.depid = c.id
where b.name = 'YourStoredProcedureNameHere'
group by b.name, c.name, c.xtype

View Extended SQL Agent Job History

Use this query to generate job and job step history; you can see this information in the SSMS GUI, but this query will allow you to better play with (read: analyze) the information. Also, if you need to configure SQL Server 2005 to keep more history than the default of 100 records per job, you can modify the history tab under SQL Agent properties in SSMS. Here is the query:


select b.name as job,
a.step_name as step,
convert(varchar(10), cast(str(a.run_date,8, 0) as datetime), 111) as date,
stuff(stuff(right('000000' + cast (a.run_time as varchar(6 ) ) ,6),5,0,':'),3,0,':') as [time],
a.run_duration as duration_seconds,
a.run_duration / 60 as duration_minutes,
case         a.run_status
when 0 then 'failure'
when 1 then 'success'
when 2 then 'retry'
when 3 then 'cancelled'
when 4 then 'active'
end as disposition,
a.message as details
from         msdb.dbo.sysjobhistory a
inner join         msdb.dbo.sysjobs b
on b.job_id = a.job_id
-- {optionally, to filter by job name} where b.name = 'MyJobName'
order by         b.name,
a.run_date,
a.run_time