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

Материал из SQL эксперт
Перейти к: навигация, поиск

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


-----------------------------------------------------------------------
    1. 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>