DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helpuser  31 Aug 14Defects Dependencies

1     
2     /*
3     ** File: helpuser
4     */
5     
6     create procedure sp_helpuser
7         @name_in_db varchar(255) = NULL,
8         @opt varchar(255) = null /* Could be "display_object" */
9     as
10    
11        declare @msg varchar(1024)
12            , @len1 int
13            , @len2 int
14            , @len3 int
15            , @sqlj_proc int /* indicates a sqlj proc */
16            , @sptlang int
17    
18    
19    
20        if @@trancount = 0
21        begin
22            set chained off
23        end
24    
25        set transaction isolation level 1
26    
27        select @sptlang = @@langid
28    
29        if @@langid != 0
30        begin
31            if not exists (
32                    select * from master.dbo.sysmessages where error
33                        between 17100 and 17109
34                        and langid = @@langid)
35                select @sptlang = 0
36        end
37    
38        set nocount on
39    
40        /*
41        ** SP_HELPUSER name_in_db, DISPLAY_OBJECT
42        ** display objects and user-defined datatypes 
43        ** owned by name_in_db in the 
44        ** current database.
45        */
46    
47        /* "display_object" is the only allowed value for @opt now */
48        if ((@opt is not null) and (@opt not in ("display_object")))
49        begin
50            /* 
51            ** 19213, "Invalid argument or unsupported command: %1!." 
52            */
53            raiserror 19213, @opt
54    
55            /* Print out the correct command usage */
56            print "Usage: sp_helpuser [{'name_in_db'| null} [, display_object]]"
57            return (1)
58        end
59    
60        if (@opt = "display_object")
61        begin
62            if @name_in_db is NULL
63            begin
64                /* 
65                ** If the user name is not specified,
66                ** get the caller's user name.
67                */
68                select @name_in_db = user_name()
69            end
70            else
71            /* 
72            ** Check if the specified user name exists
73            ** in sysusers.
74            */
75            begin
76                if not exists (select 1 from sysusers
77                        where name = @name_in_db)
78                begin
79                    /*
80                    ** 18552, "'%1!' is not a valid user of this database."
81                    */
82                    raiserror 18552, @name_in_db
83                    return (1)
84                end
85            end
86    
87            select @sqlj_proc = hextoint("0x2000000")
88    
89            /*
90            ** Instead of Triggers are sub_types of trigger
91            ** type; so first check for sysstat to be 8, then build the
92            ** prefix of "instead of" for the trigger if needed.
93            */
94            select Object_name = o.name,
95                Object_type = (case
96                    when ((o.sysstat & 15) = 8)
97                    then (case
98                        when (o.type = "IT")
99                        then "instead of "
100                       else null
101                   end)
102                   else null
103               end
104               )
105               + (m.description + x.name),
106               Create_date = o.crdate
107           into #sphelpuserdisobj
108           from sysobjects o, master.dbo.spt_values v, sysusers u,
109               master.dbo.spt_values x, master.dbo.sysmessages m
110           /* 
111           ** The following where clause is used to get the correct
112           ** string names of object_type. In order to use the right
113           ** local language, all object type names except for 
114           ** REPROT TYPES are retrieved from sysmessages.description. 
115           ** "report types" is retrieved from spt_values.name.
116           **
117           ** Error msgs 17100 - 17199: names for basic objec types
118           ** E.g.,
119           **      17100: "trigger",
120           **      17101: "system table",
121           **      etc.
122           ** Error msg 17587: "partition condition",
123           **           17588: "encrypton key",
124           **           17589: "computed column",
125           **           18903: "SQLJ function", 
126           **           18904: "SQLJ procedure".
127           */
128           where o.uid = u.uid
129               and u.name = @name_in_db
130               and o.sysstat & 2063 = v.number
131               and ((v.type = "O" and
132                       (o.type != "XP" and (o.sysstat2 & @sqlj_proc) = 0)) or
133                   (v.type = "O1" and o.type = "XP") or
134                   (v.type = "O2" and (o.sysstat2 & @sqlj_proc) != 0) or
135                   (v.type = "EK" and o.type = "EK"))
136               and v.msgnum = m.error
137               and isnull(m.langid, 0) = @sptlang
138               and ((m.error between 17100 and 17199) or
139                   (m.error between 17587 and 17589) or
140                   (m.error between 18903 and 18904))
141               /* Report Type */
142               and x.type = "R"
143               and o.userstat & - 32768 = x.number
144   
145           exec sp_autoformat @fulltabname = #sphelpuserdisobj,
146               @orderby = "order by 2, 1"
147           drop table #sphelpuserdisobj
148   
149           /* Display user-defined datatypes owned by name_in_db */
150           select User_type = s.name
151           into #sphelpuserdistype
152           from systypes s, systypes st, sysusers u
153           where s.uid = u.uid
154               and u.name = @name_in_db
155               and s.type = st.type
156               and s.usertype > 99
157               and st.name not in ("sysname", "longsysname", "nchar", "nvarchar")
158               and st.usertype < 100
159   
160           exec sp_autoformat @fulltabname = #sphelpuserdistype,
161               @orderby = "order by 1"
162           drop table #sphelpuserdistype
163           return (0)
164       end
165   
166   
167   
168       /*
169       **  If no @name_in_db, list all the users.
170       */
171       if @name_in_db is NULL
172       begin
173   
174   
175   
176           select Users_name = u.name, ID_in_db = u.uid,
177               Group_name = g.name,
178               Login_name = m.name
179           into #sphelpuser2rs
180           from sysusers u, sysusers g,
181               master.dbo.syslogins m
182           where u.suid *= m.suid
183               and u.gid *= g.uid
184               and ((u.uid < @@mingroupid and u.uid != 0)
185                   or (u.uid > @@maxgroupid))
186           exec sp_autoformat @fulltabname = #sphelpuser2rs,
187               @orderby = "order by Users_name"
188           drop table #sphelpuser2rs
189   
190   
191           return (0)
192       end
193   
194       /*
195       **  See if the @name_in_db is a user.  If so, print out the info for
196       **  the user, including users pretending to be the user (sysaliases).
197       */
198       if exists (select * from sysusers
199               where name = @name_in_db
200                   and ((uid < @@mingroupid and uid != 0)
201                       or (uid > @@maxgroupid)))
202       begin
203   
204   
205   
206           select Users_name = u.name, ID_in_db = u.uid,
207               Group_name = g.name,
208               Login_name = m.name
209           into #sphelpuser4rs
210           from sysusers u, sysusers g, master.dbo.syslogins m
211           where u.suid *= m.suid
212               and u.gid *= g.uid
213               and u.name = @name_in_db
214               and ((u.uid < @@mingroupid and u.uid != 0)
215                   or (u.uid > @@maxgroupid))
216           exec sp_autoformat @fulltabname = #sphelpuser4rs
217           drop table #sphelpuser4rs
218   
219   
220           declare @suid int
221   
222           select @suid = suid
223           from sysusers
224           where name = @name_in_db
225   
226           /*
227           ** If anyone is using @name_in_db as an alias print it out.
228           */
229           if exists (select *
230                   from sysalternates
231                   where altsuid = @suid)
232           begin
233               /* 17700, "Users aliased to user." */
234               exec sp_getmessage 17700, @msg out
235               print @msg
236               select Login_name = suser_name(suid)
237               into #sphelpuser5rs
238               from sysalternates
239               where altsuid = @suid
240               exec sp_autoformat @fulltabname = #sphelpuser5rs,
241                   @orderby = "order by Login_name"
242               drop table #sphelpuser5rs
243           end
244   
245           return (0)
246       end
247   
248       /*
249       **  Maybe the @name_in_db is a group.  If so print out the info.
250       */
251       if exists (select * from sysusers
252               where name = @name_in_db
253                   and ((uid = 0) or (uid between @@mingroupid and @@maxgroupid)))
254           and not exists (select name from master.dbo.syssrvroles
255               where name = @name_in_db)
256       begin
257           /* 17701, "The name supplied is a group name." */
258           exec sp_getmessage 17701, @msg out
259           print @msg
260           execute sp_helpgroup @name_in_db
261           return (0)
262       end
263   
264       /*
265       **  Check if @name_in_db might be aliased.
266       */
267       if exists (select *
268               from sysalternates
269               where suid = suser_id(@name_in_db))
270       begin
271           /* 17702, "The name supplied is aliased to another user." */
272           exec sp_getmessage 17702, @msg out
273           print @msg
274   
275   
276   
277   
278   
279           select Alias_name = u.name, ID_in_db = u.uid,
280               Group_name = g.name,
281               Login_name = m.name
282           into #sphelpuser7rs
283           from sysusers u, sysusers g, sysalternates a,
284               master.dbo.syslogins m
285           where u.suid *= m.suid
286               and u.gid *= g.uid
287               and ((u.uid < @@mingroupid and u.uid != 0)
288                   or (u.uid > @@maxgroupid))
289               and a.suid = suser_id(@name_in_db)
290               and a.altsuid = u.suid
291           exec sp_autoformat @fulltabname = #sphelpuser7rs
292           drop table #sphelpuser7rs
293   
294   
295           return (0)
296       end
297   
298       /*
299       **  Maybe the @name_in_db is a role.  If so print out the message.
300       */
301       if exists (select name from master.dbo.syssrvroles
302               where name = @name_in_db)
303       begin
304           /* 17705, "The supplied name is a role name. Please resubmit the 
305           ** command with the name of a user, group or aliased" 
306           */
307           raiserror 17705
308           return (1)
309       end
310   
311       /* 17703, "The name supplied is not a user, group, or aliased." */
312       raiserror 17703
313       return (1)
314   


exec sp_procxmode 'sp_helpuser', 'AnyMode'
go

Grant Execute on sp_helpuser to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 155
 QJWI 5 Join or Sarg Without Index 182
 QJWI 5 Join or Sarg Without Index 183
 QJWI 5 Join or Sarg Without Index 211
 QJWI 5 Join or Sarg Without Index 212
 QJWI 5 Join or Sarg Without Index 285
 QJWI 5 Join or Sarg Without Index 286
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MTYP 4 Assignment type mismatch @grpname: varchar(30) = varchar(255) 260
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 145
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 160
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 186
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 216
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 240
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 291
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 34
 QTYP 4 Comparison type mismatch smallint = int 34
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 156
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 158
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..syslogins  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_helpuser  
 MGTP 3 Grant to public sybsystemprocs..sysalternates  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check @@error after select into 94
 MNER 3 No Error Check should check return value of exec 145
 MNER 3 No Error Check should check @@error after select into 150
 MNER 3 No Error Check should check return value of exec 160
 MNER 3 No Error Check should check @@error after select into 176
 MNER 3 No Error Check should check return value of exec 186
 MNER 3 No Error Check should check @@error after select into 206
 MNER 3 No Error Check should check return value of exec 216
 MNER 3 No Error Check should check return value of exec 234
 MNER 3 No Error Check should check @@error after select into 236
 MNER 3 No Error Check should check return value of exec 240
 MNER 3 No Error Check should check return value of exec 258
 MNER 3 No Error Check should check return value of exec 260
 MNER 3 No Error Check should check return value of exec 272
 MNER 3 No Error Check should check @@error after select into 279
 MNER 3 No Error Check should check return value of exec 291
 MUCO 3 Useless Code Useless Brackets 48
 MUCO 3 Useless Code Useless Brackets 57
 MUCO 3 Useless Code Useless Brackets 60
 MUCO 3 Useless Code Useless Brackets 83
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 191
 MUCO 3 Useless Code Useless Brackets 245
 MUCO 3 Useless Code Useless Brackets 261
 MUCO 3 Useless Code Useless Brackets 295
 MUCO 3 Useless Code Useless Brackets 308
 MUCO 3 Useless Code Useless Brackets 313
 QCTC 3 Conditional Table Creation 94
 QCTC 3 Conditional Table Creation 150
 QCTC 3 Conditional Table Creation 176
 QCTC 3 Conditional Table Creation 206
 QCTC 3 Conditional Table Creation 236
 QCTC 3 Conditional Table Creation 279
 QISO 3 Set isolation level 25
 QNAJ 3 Not using ANSI Inner Join 108
 QNAJ 3 Not using ANSI Inner Join 152
 QNAO 3 Not using ANSI Outer Join 180
 QNAO 3 Not using ANSI Outer Join 210
 QNAO 3 Not using ANSI Outer Join 283
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
32
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
136
 VUNU 3 Variable is not used @len1 12
 VUNU 3 Variable is not used @len2 13
 VUNU 3 Variable is not used @len3 14
 MSUB 2 Subquery Marker 31
 MSUB 2 Subquery Marker 76
 MSUB 2 Subquery Marker 198
 MSUB 2 Subquery Marker 229
 MSUB 2 Subquery Marker 251
 MSUB 2 Subquery Marker 254
 MSUB 2 Subquery Marker 267
 MSUB 2 Subquery Marker 301
 MTR1 2 Metrics: Comments Ratio Comments: 30% 6
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 57 = 64dec - 9exi + 2 6
 MTR3 2 Metrics: Query Complexity Complexity: 173 6
 PRED_QUERY_COLLECTION 2 {m=master..sysmessages, o=sybsystemprocs..sysobjects, sv=master..spt_values, sv2=master..spt_values, u=sybsystemprocs..sysusers} 0 94
 PRED_QUERY_COLLECTION 2 {t=sybsystemprocs..systypes, t2=sybsystemprocs..systypes, u=sybsystemprocs..sysusers} 0 150

DEPENDENCIES
PROCS AND TABLES USED
reads table master..syslogins (1)  
writes table tempdb..#sphelpuserdisobj (1) 
reads table master..sysmessages (1)  
reads table master..spt_values (1)  
writes table tempdb..#sphelpuserdistype (1) 
reads table master..syssrvroles (1)  
writes table tempdb..#sphelpuser4rs (1) 
writes table tempdb..#sphelpuser2rs (1) 
reads table sybsystemprocs..sysusers  
reads table sybsystemprocs..sysalternates  
writes table tempdb..#sphelpuser5rs (1) 
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
calls proc sybsystemprocs..sp_helpgroup  
   writes table tempdb..#helpgroup1rs (1) 
   reads table sybsystemprocs..sysusers  
   calls proc sybsystemprocs..sp_getmessage  
   writes table tempdb..#helpgrouprs (1) 
   calls proc sybsystemprocs..sp_autoformat  
      reads table master..systypes (1)  
      calls proc sybsystemprocs..sp_autoformat  
      reads table master..syscolumns (1)  
      reads table tempdb..syscolumns (1)  
      read_writes table tempdb..#colinfo_af (1) 
      calls proc sybsystemprocs..sp_namecrack  
      reads table tempdb..systypes (1)  
   reads table master..syssrvroles (1)  
   writes table tempdb..#helpgroup2rs (1) 
   reads table sybsystemprocs..sysroles  
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_autoformat  
reads table sybsystemprocs..systypes  
writes table tempdb..#sphelpuser7rs (1)