Technical Article

Check for orphaned user or sid in syslogins

,

When rebuilding a server or setting up a backup or DR server the logins need to be added into master and synchronised with the user databases.

Inevitably there are orphaned users is sysusers or the sid's do not match with syslogins.

I use this stored procedure to check for a particular user when the user experiences a problem logging in or with their access.

Usage:
Declare  @message nvarchar(255)
    ,@return int
Exec @Return = sp_CheckUser 'username','userdbname', @message OUTPUT

Select @return, @message

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating