SQL Server/T-SQL Tutorial/System Tables Views/SYSUSERS

Selecting a list of users and their groups.

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)

SYSUSERS table

4>
5> SELECT * FROM SYSUSERS
6>
7> GO
uid    status name                                                                                                                             sid

                                                          roles
                                                                                                                                                                             createdate              upd
atedate              altuid password
                                                                                                                                               gid    environ
      hasdbaccess islogin     isntname    isntgroup   isntuser    issqluser   isaliased   issqlrole   isapprole
------ ------ -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- ---
-------------------- ------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------- ------ --------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
     0      0 public                                                                                                                           0x0105000000000009040000002E92BD1E36586647881631145F3643B
A
                                                          NULL
                                                                                                                                                                             2003-04-08 09:10:19.630 200
5-10-14 01:36:15.737      1 NULL
                                                                                                                                                    0 NULL
                0           0           0           0           0           0           0           1           0
     1      0 dbo                                                                                                                              0x01

                                                          NULL
                                                                                                                                                                             2003-04-08 09:10:19.600 200
3-04-08 09:10:19.600   NULL NULL
                                                                                                                                                    0 NULL
                1           1           0           0           0           1           0           0           0
     2      0 guest                                                                                                                            0x00

                                                          NULL
                                                                                                                                                                             2003-04-08 09:10:19.647 200
3-04-08 09:10:19.647   NULL NULL
                                                                                                                                                    0 NULL
                1           1           0           0           0           1           0           0           0
     3      0 INFORMATION_SCHEMA                                                                                                               NULL

                                                          NULL
                                                                                                                                                                             2005-10-14 01:36:06.923 200
5-10-14 01:36:06.923   NULL NULL
                                                                                                                                                    0 NULL
                0           1           0           0           0           1           0           0           0
     4      0 sys                                                                                                                              NULL

                                                          NULL
                                                                                                                                                                             2005-10-14 01:36:06.923 200
5-10-14 01:36:06.923   NULL NULL
                                                                                                                                                    0 NULL
                0           1           0           0           0           1           0           0           0
     5      0 ##MS_AgentSigningCertificate##                                                                                                   0x01060000000000090100000009FFDA09A06E24E170D86EEDDACD192
BAAEAE263
                                                          NULL
                                                                                                                                                                             2005-10-14 01:56:18.613 200
5-10-14 01:56:18.613   NULL NULL
                                                                                                                                                    0 NULL
                1           1           0           0           0           0           0           0           0
 16384      0 db_owner                                                                                                                         0x0105000000000009040000000000000000000000000000000040000
0
                                                          NULL
                                                                                                                                                                             2003-04-08 09:10:19.677 200
5-10-14 01:36:15.737      1 NULL
                                                                                                                                                16384 NULL
                0           0           0           0           0           0           0           1           0
 16385      0 db_accessadmin                                                                                                                   0x0105000000000009040000000000000000000000000000000140000
0
                                                          NULL
                                                                                                                                                                             2003-04-08 09:10:19.677 200
5-10-14 01:36:15.737      1 NULL
                                                                                                                                                16385 NULL
                0           0           0           0           0           0           0           1           0
 16386      0 db_securityadmin                                                                                                                 0x0105000000000009040000000000000000000000000000000240000
0
                                                          NULL
                                                                                                                                                                             2003-04-08 09:10:19.693 200
5-10-14 01:36:15.737      1 NULL
                                                                                                                                                16386 NULL
                0           0           0           0           0           0           0           1           0
 16387      0 db_ddladmin                                                                                                                      0x0105000000000009040000000000000000000000000000000340000
0
                                                          NULL
                                                                                                                                                                             2003-04-08 09:10:19.693 200
5-10-14 01:36:15.737      1 NULL
                                                                                                                                                16387 NULL
                0           0           0           0           0           0           0           1           0
 16389      0 db_backupoperator                                                                                                                0x0105000000000009040000000000000000000000000000000540000
0
                                                          NULL
                                                                                                                                                                             2003-04-08 09:10:19.710 200
5-10-14 01:36:15.737      1 NULL
                                                                                                                                                16389 NULL
                0           0           0           0           0           0           0           1           0
 16390      0 db_datareader                                                                                                                    0x0105000000000009040000000000000000000000000000000640000
0
                                                          NULL
                                                                                                                                                                             2003-04-08 09:10:19.710 200
5-10-14 01:36:15.737      1 NULL
                                                                                                                                                16390 NULL
                0           0           0           0           0           0           0           1           0
 16391      0 db_datawriter                                                                                                                    0x0105000000000009040000000000000000000000000000000740000
0
                                                          NULL
                                                                                                                                                                             2003-04-08 09:10:19.710 200
5-10-14 01:36:15.737      1 NULL
                                                                                                                                                16391 NULL
                0           0           0           0           0           0           0           1           0
 16392      0 db_denydatareader                                                                                                                0x0105000000000009040000000000000000000000000000000840000
0
                                                          NULL
                                                                                                                                                                             2003-04-08 09:10:19.723 200
5-10-14 01:36:15.737      1 NULL
                                                                                                                                                16392 NULL
                0           0           0           0           0           0           0           1           0
 16393      0 db_denydatawriter                                                                                                                0x0105000000000009040000000000000000000000000000000940000
0
                                                          NULL
                                                                                                                                                                             2003-04-08 09:10:19.723 200
5-10-14 01:36:15.750      1 NULL
                                                                                                                                                16393 NULL
                0           0           0           0           0           0           0           1           0
(15 rows affected)
1>