select Data.*,connections.Baglimi,connections.spid,connections.hostname from ( select SERVERNAME = @@SERVERNAME ,cduser.UserGroupCode ,cduser.UserName ,'Þifre' = CAST( CAST( Password as XML ).value('.','varbinary(max)') AS varchar ) ,prUserDatabase.DatabaseName ,CompanyCode ,OfficeCode ,StoreCode ,Kullanicitipi = (SELECT Description FROM NebimV3Master..bsField WHERE field =bsUserType.UserTypeDescription AND LangCode = 'TR') ,IsAdmin FROM dfUserPosition LEFT Outer Join (select * from NebimV3Master..cdUser) cduser on cduser.username = dfUserPosition.username Left Outer Join (select * from NebimV3Master..prUserDatabase) prUserDatabase on prUserDatabase.username = dfUserPosition.username LEFT OUTER JOIN NebimV3Master..bsUserType bsUserType ON bsUserType.UserTypeCode = cduser.UserTypeCode ) data LEFT OUTER JOIN ( SELECT DISTINCT UserName,UserGroupCode , Baglimi = 1,sysprocesses.spid,sysprocesses.hostname FROM NebimV3Master..cdUser LEFT outer JOIN sys.sysprocesses on substring(program_name,CHARINDEX('/',program_name,0)+1,NULLIF(CHARINDEX('/',program_name,CHARINDEX('/',program_name,0)+1)-CHARINDEX('/',program_name,0)-1,-1)) = UserGroupCode +'_'+UserName COLLATE DATABASE_DEFAULT WHERE sys.sysprocesses.loginame='NebimV3AdminUser' AND dbid<> DB_ID('NebimV3Master') AND dbid<> DB_ID('master') AND dbid<> DB_ID('msdb') AND dbid<> DB_ID('tempdb') ) connections ON (Connections.UserGroupCode+ Connections.UserName) = (data.UserGroupCode+data.UserName )
