SQL Server/T-SQL Tutorial/System Tables Views/SYSUSERS
Selecting a list of users and their groups.
<source lang="sql">
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)</source>
SYSUSERS table
<source lang="sql">
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></source>