Check for orphaned user or sid in syslogins

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CheckUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_CheckUser]
GO

Create Procedure sp_CheckUser
  @UserName nvarchar(65) = Null
, @DBName   nvarchar(65) = Null
, @message nvarchar(255) OUTPUT
AS
/********1*********2*********3*********4*********5*********6*********8*********9*********0*********1*********2*****
**
**  $Archive$
**  $Revision$
**  $Author$ 
**  $Modtime$
**
*******************************************************************************************************************
**
**  $Log$
**
*******************************************************************************************************************
**
**Name: sp_CheckUser
**Desc: Used to check if sid matches between given database sysusers and the master..sysxlogins tables
**
**NOTE: Username can be standard or trusted login name. sysusers tends to drop the DOMAIN\ part of the login
**
**Return values: 0User name and Login sid's match
**-1No User name supplied
**              -2Login doesn't exist in master i.e. orphan user
**-3Multiple Usernames or sid's exist. Refine search or check that login is not repeated in MIXED security
**-4The sid's do not match for the user name.
*******************************************************************************************************************
**Change History - All Author comments below this point.
*******************************************************************************************************************
**  AuthorDateDescription
**  ----------------------------------------------------------
**  Neil Jacobson07-Jan-2002Original - First Revision
******************************************************************************************************************/
Declare 
  @Err int
, @ret int
, @rows int
, @result nvarchar(110)
, @cmd nvarchar(255)
, @uid smallint   
, @sysusrname nvarchar(255)  
, @usersid varbinary (85)  
, @sysloginname nvarchar(255) 
, @loginsid varbinary (85)
set nocount on
If @username is null
Begin
Select @ret = -1, @message = 'You must supply a user name for checking.'
RETURN @ret
End
If not(@DBName is null)
Begin
select @cmd = 'Use ' + @DBName 
EXEC (@cmd)
End

Select @cmd = 'Select [name], sid, uid INTO ##tmptblsysusers from ' + @DBName + '.dbo.sysusers where [name] like ''%' + @username + '%'''
EXEC (@cmd)

Select @Err = @@Error , @rows = @@Rowcount

If @Err <> 0
Begin
drop table ##tmptblsysusers
Return @Err
End
If @Rows = 0
Begin
Select 'The user does not exist in the database ' + @DBName
End
If @rows = 1
Begin
Select @sysusrname = [name], @usersid = sid, @uid = uid from ##tmptblsysusers
Select @result = 'There is a user in database ' + @DBName + '. The Username = ' + rtrim(@sysusrname) + ' ,uid = ' + rtrim(Cast(@uid as varchar(90))) + ' , sid = '  
Select @result, @usersid
End
If @Rows > 1
Begin
Select @result = 'There is more than one user in database ' + @DBName + ' with a matching username. These are the matching user names.'  
Select @result
Select * from ##tmptblsysusers
End

Select @cmd = 'Select name, sid INTO ##tmptblsyslogins from master.dbo.sysxlogins where [name] like ''%' + @username + '%'''
EXEC (@cmd)

Select @Err = @@Error , @rows = @@Rowcount
If @Err <> 0
Begin
drop table ##tmptblsysusers
drop table ##tmptblsyslogins
Return @Err
End
If @Rows = 0
Begin
Select @message = 'The login does not exist in master.dbo.sysxlogins ' + @DBName
drop table ##tmptblsysusers
drop table ##tmptblsyslogins
Return -2
End
If @Rows =1
Begin
Select @loginsid = sid from ##tmptblsyslogins
Select 'There is a login in in master.dbo.sysxlogins with a sid = ' , @loginsid
End
If @Rows > 1
Begin
Select * from ##tmptblsyslogins
Select @ret = -3, @message = 'There are multiple sid''s in database ' + @DBName + ' or sysxlogins.'
drop table ##tmptblsysusers
drop table ##tmptblsyslogins
Return @Ret
End


If @loginsid = @usersid
Begin
Select @ret = 0, @message = 'The sid in database ' + @DBName + ' matches with sysxlogins.'
drop table ##tmptblsyslogins
drop table ##tmptblsysusers
RETURN @ret
End
Select @ret = -4, @message = 'The sid in database ' + @DBName + ' DOES NOT match with sysxlogins.'
drop table ##tmptblsyslogins
drop table ##tmptblsysusers
RETURN @ret


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO