Selecting a list of users and their groups. : SYSUSERS « System Tables Views « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. System Tables Views
3. SYSUSERS

4>
5>
6>     SELECT    user_name(s1.uid) AS UserName,
7>               user_name(s2.uid) as GroupName
8>     FROM      sysusers s1
9>               JOIN sysusers s2 ON s1.gid = s2.uid
10>     WHERE     s1.uid <> s1.gid
11>     UNION
12>     SELECT    user_name(s1.uid) AS UserName,
13>               'public' as GroupName
14>     FROM      sysusers s1
15>               JOIN sysusers s2 ON s1.gid = s2.uid
16>     WHERE     s1.uid <> s1.gid
17>     AND       s1.gid <> user_id('public')
18>     ORDER BY  UserName, GroupName
19>     GO
UserName                                                                                                                         GroupName

-------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------
---------------------------------------------------------
##MS_AgentSigningCertificate##                                                                                                   public

dbo                                                                                                                              public

guest                                                                                                                            public

INFORMATION_SCHEMA                                                                                                               public

sys                                                                                                                              public


(5 rows affected)