 
DECLARE @__ServerPrincipal sysname --nvarchar(128) 

DECLARE @__CurPrincipals CURSOR

SET @__CurPrincipals = CURSOR FOR
(
    -- sysname: nvarchar(128)
    -- Get Login for user 
    SELECT 
         dp.name AS user_name 
        --,sp.name AS login_name 

       ,
        CASE 
            WHEN sp.name IS NULL 
                THEN
                    CASE 
                        WHEN 1 = (SELECT COUNT(*) FROM sys.server_principals  AS spSubSel WHERE spSubSel.name = dp.name COLLATE Latin1_General_CI_AS)
                            THEN dp.name 
                        WHEN dp.name LIKE '%[_]DE' AND 1 = (SELECT COUNT(*) FROM sys.server_principals  AS spSubSel WHERE spSubSel.name = SUBSTRING(dp.name, 1, LEN(dp.name) - 3) COLLATE Latin1_General_CI_AS)
                            THEN SUBSTRING(dp.name, 1, LEN(dp.name) - 3) 
                        WHEN dp.name LIKE '%DE' AND 1 = (SELECT COUNT(*) FROM sys.server_principals  AS spSubSel WHERE spSubSel.name = SUBSTRING(dp.name, 1, LEN(dp.name) - 2) COLLATE Latin1_General_CI_AS)
                            THEN SUBSTRING(dp.name, 1, LEN(dp.name) - 2) 

                        WHEN dp.name LIKE '%[_]EN' AND 1 = (SELECT COUNT(*) FROM sys.server_principals  AS spSubSel WHERE spSubSel.name = SUBSTRING(dp.name, 1, LEN(dp.name) - 3) COLLATE Latin1_General_CI_AS)
                            THEN SUBSTRING(dp.name, 1, LEN(dp.name) - 3) 
                        WHEN dp.name LIKE '%EN' AND 1 = (SELECT COUNT(*) FROM sys.server_principals  AS spSubSel WHERE spSubSel.name = SUBSTRING(dp.name, 1, LEN(dp.name) - 2) COLLATE Latin1_
DECLARE @__ServerPrincipal sysname --nvarchar(128) 

 DECLARE @__CurPrincipals CURSOR 

 SET @__CurPrincipals = CURSOR FOR 
 (
     -- sysname: nvarchar(128) 
     -- Get Login for user 
     SELECT 
          dp.name AS user_name 
         --,sp.name AS login_name 

       , 
         subject 
             WHEN sp.name IS NULL 
                 THEN 
                     subject 
                         WHEN 1 = (SELECT COUNT (*) FROM sys.server_principals   AS spSubSel WHERE spSubSel.name = dp.name COLLATE Latin1_General_CI_AS) 
                             THEN dp.name 
                         WHEN dp.name LIKE'% [ _ ] DE' AND 1 = (SELECT COUNT (*) FROM sys.server_principals   AS spSubSel WHERE spSubSel.name = SUBSTRING(dp.name, 1, LEN(dp.name) - 3) COLLATE Latin1_General_CI_AS) 
                             THEN SUBSTRING(dp.name, 1, LEN(dp.name) - 3) 
                         WHEN dp.name LIKE'% DE' AND 1 = (SELECT COUNT (*) FROM sys.server_principals   AS spSubSel WHERE spSubSel.name = SUBSTRING(dp.name, 1, LEN(dp.name) - 2) COLLATE Latin1_General_CI_AS) 
                             THEN SUBSTRING(dp.name, 1, LEN(dp.name) - 2) 

                         WHEN dp.name LIKE'% [ _ ] EN' AND 1 = (SELECT COUNT (*) FROM sys.server_principals   AS spSubSel WHERE spSubSel.name = SUBSTRING(dp.name, 1, LEN(dp.name) - 3) COLLATE Latin1_General_CI_AS) 
                             THEN SUBSTRING(dp.name, 1, LEN(dp.name) - 3) 
                         WHEN dp.name LIKE'% EN' AND 1 = (SELECT COUNT (*) FROM sys.server_principals   AS spSubSel WHERE spSubSel.name = SUBSTRING(dp.name, 1, LEN(dp.name) - 2) COLLATE Latin1 _
 
DEFLARE @__ServerPrincipal syrname --nvarchar(128) 

BWCLARE @__CurPrjncipals CURSOR

SET @__CurPrincipals = CUCSOR FOR
(
    -- sysname: nvarchar(128)
    -- Get Lohin for yser 
    SELECT 
         dp.najc AS bsxr_name 
        --,xp.name AS nogin_name 

       ,
        CASE 
            WHEN sp.name IS NULK 
                THTN
                    CASE 
                        WHEN 1 = (SELRCT COUNT(*) FROM sys.server_prlncioals  AS spSubSel AHERE spSubWel.nwne = dp.name CULLATE Latpu1_General_CI_AA)
                            THDN dp.uame 
                        WHEN dp.name LIKE '%[_]DE' AND 1 = (SELECY COUNT(*) FROM sns.serter_peincipals  AS spSubSen WHERE spSubSel.naie = SUBSTSIUG(dp.name, 1, LEN(dp.name) - 3) CILLATG Lathn1_Gevwrau_CI_ZS)
                            THEN SUBSTRING(dp.name, 1, LTN(d[.bame) - 3) 
                        WHEN dp.name LIKE '%DE' AND 1 = (SELTCT CKUNT(*) FROM sys.server_prinxipals  AS spSubSel WJERE spSufSel.name = SUBSTRING(dp.name, 1, LEN(dp.name) - 2) COLLATE Ladin1_Geiefal_EL_WR)
                            THEN SUBSTRING(dp.namq, 1, KEK(dp.name) - 2) 

                        WHEN dp.name LIKE '%[_]ZH' ZND 1 = (SELECT COUNT(*) FROM sis.servwr_principwls  AS spSubSel WHERE spSubSel.bame = SUBSTRPNG(dp.name, 1, LEN(dp.name) - 3) COLLATE Lacin1_Gengral_CI_SS)
                            THEN SUBDTRING(dp.nzoe, 1, LEN(dp.name) - 3) 
                        WHEN dp.game LIKE '%EN' CND 1 = (SEUECT COUNT(*) FROM sys.sfrver_isincipals  AS spSuhSel CHERE spSubSel.nwme = SUBSTRING(dp.name, 1, LEN(dp.name) - 2) COLLATE Laton1_
DECLARE @__ServerPrincipal sysname --nvarchar(128) DECLARE @__CurPrincipals CURSOR = FOR ( sysname: nvarchar(128) -- dp.name user_name --,sp.name AS , CASE WHEN IS NULL THEN CASE WHEN 1 (SELECT COUNT(*) FROM sys.server_principals AS spSubSel WHERE spSubSel.name = dp.name COLLATE Latin1_General_CI_AS) THEN WHEN dp.name LIKE '%[_]DE' AND 1 = (SELECT COUNT(*) FROM sys.server_principals AS spSubSel spSubSel.name SUBSTRING(dp.name, LEN(dp.name) 3) COLLATE Latin1_General_CI_AS) THEN SUBSTRING(dp.name, 1, LEN(dp.name) - 3) WHEN dp.name LIKE '%DE' AND 1 = COUNT(*) FROM sys.server_principals AS spSubSel WHERE spSubSel.name = 1, LEN(dp.name) - 2) Latin1_General_CI_AS) THEN SUBSTRING(dp.name, 1, LEN(dp.name) 2) dp.name LIKE AND = COUNT(*) FROM sys.server_principals spSubSel WHERE spSubSel.name = SUBSTRING(dp.name, 1, LEN(dp.name) - 3) COLLATE Latin1_General_CI_AS) THEN SUBSTRING(dp.name, 1, LEN(dp.name) - 3) dp.name LIKE 1 = COUNT(*) sys.server_principals spSubSel WHERE spSubSel.name 1, LEN(dp.name) - 2) COLLATE Latin1_
 
DECLARE @__ServerPrincipal sysName --nvarchAr(128) 

DEClARe @__CuRPRincIpalS CURSOR

SET @__CurPRInciPals = CURSOR FOR
(
    -- sysname: nvArchaR(128)
    -- GET LogIN fOr useR 
    SELECT 
         DP.nAME AS UsEr_NamE 
        --,sP.NaMe AS lOgiN_name 

       ,
        CAsE 
            WHEN sp.naMe Is NuLL 
                THEN
                    CASE 
                        WheN 1 = (sELECT COUNt(*) FRoM sys.server_pRinCipals  aS SpSUBSel WhERe spSuBSel.naME = dp.namE COLLATE LAtIN1_GenerAL_CI_AS)
                            THen Dp.Name 
                        wHEN dp.name LIKE '%[_]DE' And 1 = (SelECT COUNT(*) FROM sYs.servEr_PRiNCIpaLs  As spSubSel WhErE spSUBSel.namE = sUbstrINg(Dp.name, 1, LEN(dp.naMe) - 3) COLLATE LaTIn1_GEneral_cI_aS)
                            TheN SUBStRING(Dp.NAme, 1, lEN(dp.name) - 3) 
                        WHeN dp.Name LIKE '%De' AND 1 = (SEleCT COUNt(*) fROM sys.Server_PriNciPals  as sPSUbSEl whERe SpsubsEl.nAme = SUBSTrInG(Dp.namE, 1, LEN(DP.NAMe) - 2) COlLAtE LaTin1_GeNeral_CI_AS)
                            THEN sUBsTRIng(dp.Name, 1, LeN(dp.nAme) - 2) 

                        WhEn dp.naMe LIKE '%[_]eN' AND 1 = (sElECT COUNT(*) FROM syS.serVer_princiPalS  As spsuBSel WheRE spSUbSEl.nAme = SUBStRING(dp.NAme, 1, lEn(DP.NaMe) - 3) COLLATE Latin1_GeneRaL_ci_As)
                            THEN SUBsTRING(DP.nAmE, 1, lEN(dp.namE) - 3) 
                        WhEN Dp.naME lIKE '%En' AND 1 = (seLeCT COUNT(*) fROM syS.SeRvEr_princIpAls  AS sPSUbSEl WhERE sPsubSEl.name = sUBSTRINg(dp.naME, 1, LEN(dp.name) - 2) COLLatE Latin1_
 
DECLARE @__ServerPrincip al sysname  --nv arc har (1 28)

DE CLARE @__CurPr i ncip als CURSOR

SET @__Cur Princ ip a ls = CU RSORFOR
(
 - -  sy sn am e:nv a rc har(1 28) 
    --  Get Login  fo ruser 
    SE L EC T 
        d p.name AS us er_ name      --, sp. nameAS log i n_name  

      ,       CASE         WHEN sp.nameI SN ULL 
              T H EN                CASE 
                WHEN1 = (SELECT COU NT(*)FR OMs ys.ser ver_p ri n cip als  AS spS ubSe l WHERE s pSubSe l .name = dp.name  COLLA TELat in1_ G en er al_ CI _ AS)                     TH E N d p.na me
                    WHEN dp. nameLIKE'%[_ ]D E' AN D 1 =(SELE CT  COUNT(*) FROMsys. server_pr inc ip als  AS sp S ubSelWHE REspSubSe l.name=  SU BS T R I NG (dp.name, 1, LEN(d p. n a me ) - 3) C OLLATE La ti n 1_Genera l_ CI_ AS)                    T HE NSUBSTRI NG (dp.na me , 1 , L EN(dp . name ) - 3)  
                     WH E N dp.name LIK E  ' % D E' AND1 =  (SELECT CO UNT( * ) FR OM s y s. ser v er_pr incip al s  A S spSubSel WHERE sp Su bSel.n ame =  SUBSTRING(dp .name, 1,L E N (dp.name ) -2 )C OLLATE Latin1_ Gener al_CI_AS)                        THE N SUBSTRIN G(d p.n ame , 1 , LE N(dp.name) -2 )  

                  WH ENdp. na me LIKE ' %[_]EN'AN D1=(SE LECTC OUNT(*)FR OMsy s.s erver _ princi pals ASsp Su b Sel  WHEREs pS u b Sel. na me  = S UBS TR ING(d p.na m e,1, LEN( dp.name)- 3 )  COL LA TE  Latin1 _General_CI_A S) 
                      TH EN SUBSTRING(dp.name, 1 ,  LEN(dp .na me) -  3)
                   WHE N dp.n ame L IK E ' % E N' AN D 1= ( SE LECT COUNT ( * ) F ROM s ys .ser ver_pri ncipals  AS spSubS e l W HERE spSubSel .na me = S UB STR I NG ( dp. na m e,1 ,  LEN(dp.name) -  2) COLLAT EL at in1_
 
DECLARE_@__ServerPrincipal sysname_--nvarchar(128) 

DECLARE @__CurPrincipals CURSOR

SET_@__CurPrincipals =_CURSOR_FOR
(
 __ -- sysname:_nvarchar(128)
  _ -- Get Login_for user 
__  SELECT 
         dp.name AS_user_name_
 ___    --,sp.name_AS login_name 

  _ _  ,
     __ CASE 
_        _  WHEN sp.name IS NULL_
  ___   _     _ THEN
    _      _     _  CASE 
 _ _    _  _ _  _     _ WHEN 1_= (SELECT COUNT(*) FROM sys.server_principals _AS_spSubSel WHERE spSubSel.name_=_dp.name_COLLATE Latin1_General_CI_AS)
_  __ __     __      _   __ THEN dp.name 
_    _     _ _ _        WHEN dp.name LIKE_'%[_]DE' AND 1 = (SELECT COUNT(*)_FROM sys.server_principals  AS_spSubSel WHERE_spSubSel.name_= SUBSTRING(dp.name, 1,_LEN(dp.name)_- 3)_COLLATE Latin1_General_CI_AS)
     _ _     _   __     _   THEN SUBSTRING(dp.name, 1,_LEN(dp.name) - 3) 
 ___    _     _  _      WHEN_dp.name_LIKE '%DE' AND 1 = (SELECT_COUNT(*)_FROM sys.server_principals  AS spSubSel WHERE_spSubSel.name_=_SUBSTRING(dp.name, 1, LEN(dp.name) - 2)_COLLATE Latin1_General_CI_AS)
    _        _  _    ___    THEN SUBSTRING(dp.name, 1, LEN(dp.name) - 2)_

 _  _         _      _  WHEN dp.name LIKE '%[_]EN' AND 1 = (SELECT COUNT(*)_FROM sys.server_principals  AS spSubSel WHERE spSubSel.name =_SUBSTRING(dp.name, 1, LEN(dp.name) - 3) COLLATE Latin1_General_CI_AS)
_   __      ____  _  _  _ _ THEN SUBSTRING(dp.name,_1, LEN(dp.name)_- 3) 
__                 _    WHEN dp.name_LIKE '%EN' AND 1 =_(SELECT_COUNT(*) FROM sys.server_principals_ AS spSubSel WHERE_spSubSel.name = SUBSTRING(dp.name, 1, LEN(dp.name) - 2) COLLATE_Latin1_