users and roles listed separately from one query.

• SQLIndia

  Ten Centuries

  Points: 1073

  More actions

  May 12, 2005 at 7:30 am

  #167060

  Hi Folks

  I have this query for user and roles from sysuers and sysmember table.. i want user name will be in separate column and roles in separate coulmn means two columns by running same query..

  Please help....

   

  SELECT     a.name

  FROM         db_2_3.dbo.sysusers a INNER JOIN

                        db_2_3.dbo.sysmembers b ON a.uid <> b.memberuid

  WHERE     (a.name NOT LIKE 'db%') AND (a.name NOT IN ('guest', 'public'))

   

  Thanx in advance

   

  SqlIndia

• May 16, 2005 at 8:00 am

  This was removed by the editor as SPAM

• bersileus sacamay-126226

  SSChasing Mays

  Points: 642

  More actions

  May 16, 2005 at 8:09 pm

  #559160

  take your pick

  SELECT     a.name, '' as role

  FROM         dbo.sysusers a INNER JOIN

                        dbo.sysmembers b ON a.uid <> b.memberuid

  WHERE     (a.name NOT LIKE 'sa%') AND (a.name NOT IN ('guest', 'public'))

  union

  SELECT     '' as name, a.name as role

  FROM         dbo.sysusers a INNER JOIN

                        dbo.sysmembers b ON a.uid <> b.memberuid

  WHERE     (a.name NOT LIKE 'sa%') AND (a.name NOT IN ('guest', 'public'))

  --or

  SELECT     a.name, (

  SELECT     a1.name

  FROM         dbo.sysusers a1 INNER JOIN

                        dbo.sysmembers b1 ON a1.uid <> b1.memberuid

  WHERE     a.name = a1.name AND (a1.name NOT LIKE 'sa%') AND (a1.name NOT IN ('guest', 'public'))

  ) as role

  FROM         dbo.sysusers a INNER JOIN

                        dbo.sysmembers b ON a.uid <> b.memberuid

  WHERE     (a.name NOT LIKE 'sa%') AND (a.name NOT IN ('guest', 'public'))

  hope this helps