Find Orphan users from all databases exists in the SQL server

It is a typical requirement to find and fix the Orphan users in restored database to access the database with its underlying privileges.  In order to achieve this I have prepared a small script to find the Orphan users in all the databses exists in server.  Hope this helps to all, who are looking for this info in MS Sql server.

Script :

Declare @dbs table (id int identity(1,1), db varchar(100))

insert into @dbs (db)
select name from master.sys.databases where database_id > 4 order by name

--select * from @dbs


Declare @findOrphans table (db varchar(100), Orpuser varchar (100),USID varchar(200))
Declare @fixorphans table (db varchar(100), Script varchar (500))


Declare @a varchar(1000)
Declare @b tinyint
Declare @c tinyint
Declare @d varchar(100)

set @b = 1
select @c = MAX (id) from @dbs

while @b < @c
Begin
select @d = db from @dbs where id=@b
select @a = db+'.dbo.sp_change_users_login report ' from @dbs where id = @b
insert into @findorphans (Orpuser, UsID) exec (@a)
Update @findOrphans set db = @d where db is null
set @b = @b+1
end

select * from @findorphans

Slipstream Installation in Sql server 2008

Sql server installation has ability called slipstream which performs the service pack installation along with the software installation.  This saves time, pleas refer below link.

http://support.microsoft.com/kb/955392

Findout SQL server Job notification details

How to findout all the notification details which are configured in the server, like there are some jobs which are not sending notifications for successive / failure actions, so we need to fix that. By identifying each and everyone manually it takes a long time when there are no. of jobs, so following query will help to identify the notification details.

select a.name, case when a.enabled =1 then 'Enabled' else 'Disabled' end Status, case when notify_level_email = 1 then 'Job success' when notify_level_email = 2 then 'Job failure' end NotifyEmail, b.name
from sysjobs a left outer join (Select * from Sysoperators ) b on a.notify_email_operator_id = b.id
union all
select a.name,case when a.enabled =1 then 'Enabled' else 'Disabled' end Status, case when notify_level_page = 1 then 'Job success' when notify_level_page = 2 then 'Job failure' end NotifyPage, c.name
from sysjobs a left outer join (Select * from Sysoperators ) c on a.notify_page_operator_id = c.id order by a.name