DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dropuser  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /*	4.8	1.1	06/14/90	sproc/src/defaultlanguage */
4     
5     /*
6     ** Messages for "sp_dropuser"           17543
7     **
8     ** 17232, "No user with the specified name exists in the current database."
9     ** 17289, "Set your curwrite to the hurdle of current database."
10    ** 17431, "true"
11    ** 17432, "false"
12    ** 17543, "You cannot drop the 'database owner'."
13    ** 17544, "You cannot drop the 'guest' user from master database or a temporary
14    **         database."
15    ** 17545, "You cannot drop user because user '%1!' owns objects in database."
16    ** 17546, "You cannot drop user because user '%1!' owns types in database."
17    ** 17547, "The dependent aliases were also dropped."
18    ** 17548, "User has been dropped from current database."
19    ** 17549, "You cannot drop user because user '%1!'  owns grantable
20    **         privileges and granted them to other users.  The user
21    ** 	   has granted the following privileges:"
22    ** 17673, "All"
23    ** 17756, "The execution of the stored procedure '%1!' in database
24    **         '%2!' was aborted because there was an error in writing the
25    **         replication log record."
26    ** 18033, "You cannot drop user because user '%1!' owns thresholds in database."
27    ** 18053, "User '%1!' was granted grantable privileges by the following users:"
28    ** 18075, "Set your maxwrite label correctly."
29    ** 18554, "You cannot drop user '%1!' because stored proc '%2!' owned by it 
30    **	   is bound to an execution class. Use sp_unbindexeclass before 
31    **	   dropping user."
32    ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'"
33    ** 18797, "Unable to find a user with name '%1!' and login id '%2!' in sysusers."
34    ** 18815, "You cannot drop user '%1!' because it still owns objects, types, thresholds, privileges, and/or execution classes on the companion server '%2!'.  Issue stored procedure '%3!' on server '%4!' for details."
35    ** 19941, "You cannot drop the user because the suid value for user '%1!' is
36    **        not unique in the database."
37    */
38    
39    /* 
40    ** IMPORTANT: Please read the following instructions before
41    **   making changes to this stored procedure.
42    **
43    **	To make this stored procedure compatible with High Availability (HA),
44    **	changes to certain system tables must be propagated 
45    **	to the companion server under some conditions.
46    **	The tables include (but are not limited to):
47    **		syslogins, sysservers, sysattributes, systimeranges,
48    **		sysresourcelimits, sysalternates, sysdatabases,
49    **		syslanguages, sysremotelogins, sysloginroles,
50    **		sysalternates (master DB only), systypes (master DB only),
51    **		sysusers (master DB only), sysprotects (master DB only)
52    **	please refer to the HA documentation for detail.
53    **
54    **	Here is what you need to do: 
55    **	For each insert/update/delete statement, add three sections to
56    **	-- start HA transaction prior to the statement
57    **	-- add the statement
58    **	-- add HA synchronization code to propagate the change to the companion
59    **
60    **	For example, if you are adding 
61    **		insert master.dbo.syslogins ......
62    **	the code should look like:
63    **	1. Before that SQL statement:
64    **		
65    **	2. Now, the SQL statement:
66    **		insert master.dbo.syslogins ......
67    **	3. Add a HA synchronization section right after the SQL statement:
68    **		
69    **
70    **	You may need to do similar change for each built-in function you
71    **	want to add.
72    **
73    **	After that, you need to add a separate part at a place where it can not
74    **	be reached by the normal execution path:
75    **	clean_all:
76    **		
77    **		return (1)
78    */
79    
80    create procedure sp_dropuser
81        @name_in_db varchar(255) /* user name to drop */
82    as
83    
84        declare @suid int /* suid of the user */
85        declare @uid int /* uid of the user */
86        declare @objectcount int /* count of objects user owns */
87        declare @typecount int /* count of types user owns */
88        declare @grantcount int /* count of grants user made */
89        declare @userdropped int /* flag to indicate user was dropped */
90        declare @threshcount int /* count of thresholds bound by user */
91        declare @spexebndcount int /* cnt of user owned sp exe bindings */
92        declare @sp_name varchar(255) /* name of stored proc */
93        declare @msg varchar(1024)
94        declare @msg_all varchar(30) /* msg for "all" equivalent */
95        declare @msg_true varchar(30) /* msg for "true" equivalent */
96        declare @msg_false varchar(30) /* msg for "false" equivalent */
97        declare @dummy int
98        declare @dbname varchar(255)
99        declare @tempdb_mask int /* All database status bit for a tempdb */
100       declare @isatempdb int /* is database a temporary database */
101       declare @err1 int /* temp. variable to store @@error */
102   
103       declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
104       declare @retstat int
105   
106   
107       select @HA_CERTIFIED = 0
108   
109   
110   
111       /* check to see if we are using HA specific SP for a HA enabled server */
112       exec @retstat = sp_ha_check_certified 'sp_dropuser', @HA_CERTIFIED
113       if (@retstat != 0)
114           return (1)
115   
116       if @@trancount = 0
117       begin
118           set chained off
119       end
120   
121       set transaction isolation level 1
122   
123       /*
124       **  Only the Database Owner (DBO) or
125       **  Accounts with SA or SSO role can execute it.
126       **  Call proc_role() with the required SA role.
127       */
128       if (user_id() != 1)
129       begin
130           if (charindex("sa_role", show_role()) = 0 and
131                   charindex("sso_role", show_role()) = 0)
132           begin
133               select @dummy = proc_role("sa_role")
134               select @dummy = proc_role("sso_role")
135               return (1)
136           end
137       end
138   
139       if (charindex("sa_role", show_role()) > 0)
140           select @dummy = proc_role("sa_role")
141   
142       if (charindex("sso_role", show_role()) > 0)
143           select @dummy = proc_role("sso_role")
144   
145       /*
146       **  See if the user exists in the database.
147       */
148       select @uid = uid, @suid = suid from sysusers
149       where name = @name_in_db and
150           ((uid < @@mingroupid and uid != 0) or (uid > @@maxgroupid))
151   
152       /*
153       **  No such user so return.
154       */
155       if @uid is NULL
156       begin
157           /*
158           ** 17232, "No user with the specified name exists in the current database."
159           */
160           raiserror 17232
161           return (1)
162       end
163   
164   
165   
166   out_of_HA_checking:
167   
168       /*
169       **  Big trouble if dbo (uid = 1) is dropped so check.
170       */
171       if @uid = 1
172       begin
173           /*
174           ** 17543, "You cannot drop the 'database owner'."
175           */
176           raiserror 17543
177           return (1)
178       end
179   
180       /*
181       ** Check to see if we are dealing with a temporary database.
182       */
183       select @tempdb_mask = number
184       from master.dbo.spt_values
185       where type = "D3" and name = "TEMPDB STATUS MASK"
186   
187       if (db_id() = 2) or exists (select * from master..sysdatabases
188               where dbid = db_id()
189                   and (status3 & @tempdb_mask) != 0)
190       begin
191           select @isatempdb = 1
192       end
193   
194       /*
195       **  Trouble if guest gets dropped from master or tempdb, so check.
196       */
197       if (@name_in_db = "guest" and (db_id() = 1 or @isatempdb = 1))
198       begin
199           /*
200           ** 17544, "You cannot drop the 'guest' user from master or tempdb."
201           */
202           raiserror 17544
203           return (1)
204       end
205   
206       /*
207       **  If the user owns any objects or datatypes and we're not
208       **  forcing the drop, return without doing anything.
209       */
210       select @objectcount = count(*)
211       from sysobjects
212       where uid = @uid
213       select @typecount = count(*)
214       from systypes
215       where uid = @uid
216       select @grantcount = count(*)
217       from sysprotects
218       where grantor = @uid
219           and id not in (select id from sysobjects
220               where uid = @uid)
221       select @threshcount = count(*)
222       from systhresholds
223       where suid = @suid
224   
225       select @spexebndcount = count(*)
226       from sysattributes
227       where (class = 6 and object_type = 'PR' and object_info1 = @uid)
228   
229       if @objectcount > 0 or @typecount > 0 or @grantcount > 0 or @threshcount > 0 or
230           @spexebndcount > 0
231       begin
232           if @objectcount > 0
233           begin
234               /*
235               ** 17545, "You cannot drop user because user '%1!' owns objects in database."
236               */
237               raiserror 17545, @name_in_db
238               select name, type
239               from sysobjects
240               where uid = @uid
241           end
242   
243           if @typecount > 0
244           begin
245               /*
246               ** 17546, "You cannot drop user because user '%1!' owns types in database."
247               */
248               raiserror 17546, @name_in_db
249               select user_type = a.name, physical_type = b.name
250               from systypes a, systypes b
251               where a.uid = @uid
252                   and a.type = b.type
253                   and b.usertype < 100
254           end
255   
256           if @grantcount > 0
257           begin
258   
259               /*
260               ** 17431, "true"
261               ** 17432, "false"
262               */
263               exec sp_getmessage 17431, @msg_true output
264               exec sp_getmessage 17432, @msg_false output
265               /* 17673, "All" */
266               exec sp_getmessage 17673, @msg_all out
267   
268               /* create and populate temp tables so that we can map 
269               ** column bit map to names 
270               */
271               create table #sysprotects1(id int, uid int,
272                   action smallint,
273                   protecttype tinyint,
274                   grantor int, number int)
275   
276               create table #sysprotects2(id int, col_count smallint)
277   
278               insert into #sysprotects2(id, col_count)
279               select id, count(*)
280               from syscolumns
281               group by id
282   
283               insert into #sysprotects1(id, uid, action, protecttype,
284                   grantor, number)
285               select distinct
286                   p.id, p.uid, p.action, p.protecttype,
287                   p.grantor, c.number
288               from sysprotects p, master.dbo.spt_values c
289               where (~ isnull(convert(tinyint, substring(p.columns, c.low, 1)), 0) & c.high != 0
290                       and c.number <= (select col_count from #sysprotects2 where id = p.id))
291                   and c.type = "P"
292                   and c.number <= 1024
293                   and p.columns is not null
294                   and convert(tinyint, substring(p.columns, 1, 1)) & 0x1 != 0
295                   and (convert(tinyint, substring(p.columns, 1, 1)) & 0xfe != 0
296                       or substring(p.columns, 2, 1) is not null)
297   
298               insert into #sysprotects1(id, uid, action, protecttype,
299                   grantor, number)
300               select distinct
301                   p.id, p.uid, p.action, p.protecttype,
302                   p.grantor, c.number
303               from sysprotects p, master.dbo.spt_values c
304               where convert(tinyint, substring(isnull(p.columns, 0x1), c.low, 1))
305                   & c.high != 0
306                   and c.type = "P"
307                   and c.number <= 1024
308                   and (p.columns is null
309                       or convert(tinyint, substring(p.columns, 1, 1)) & 0x1 = 0
310                       or (convert(tinyint, substring(p.columns, 1, 1)) & 0xfe = 0
311                           and substring(p.columns, 2, 1) is null))
312   
313               /* Set nocount on to avoid intermingling of output */
314               set nocount on
315   
316               /*
317               ** 17549, "You cannot drop user because user '%1!' owns grantable
318               **         privileges and granted them to other users.  The user
319               ** 	   has granted the following privileges:"
320               */
321               raiserror 17549, @name_in_db
322               print " "
323   
324               select grantee = u.name,
325                   object = o.name,
326                   column = substring(isnull(col_name(p.id, p.number),
327                           @msg_all), 1, 10),
328                   privilege = s.name
329               from sysusers u, sysobjects o, #sysprotects1 p,
330                   master.dbo.spt_values s
331               where p.grantor = @uid and p.uid = u.uid and p.protecttype < 2
332                   and p.id = o.id and s.number = p.action
333                   and s.name is not NULL
334                   and s.type = 'T'
335               order by grantee, object, privilege
336   
337               /*
338               ** 18053, "User '%1!' was granted grantable privileges by the
339               **         following users:"
340               */
341               exec sp_getmessage 18053, @msg output
342               print " "
343               print @msg, @name_in_db
344   
345               select distinct grantor = u.name
346               from sysusers u, sysprotects p
347               where p.uid = @uid and u.uid = p.grantor and p.protecttype = 0
348               order by grantor
349           end
350   
351           if @threshcount > 0
352           begin
353               /*
354               ** 18033, "You cannot drop user because user '%1!' owns thresholds in database."
355               */
356               raiserror 18033, @name_in_db
357               select "Segment name" = g.name, "Free pages" = t.free_space
358               from syssegments g, systhresholds t
359               where t.suid = @suid
360                   and t.segment = g.segment
361           end
362   
363           if @spexebndcount > 0
364           begin
365               select @sp_name = object_cinfo from sysattributes
366               where (class = 6 and object_type = 'PR' and object_info1 = @uid)
367               /*
368               ** 18554, "You cannot drop user '%1!' because stored proc '%2!'
369               **	 owned by it is bound to an execution class. Use 
370               **	 sp_unbindexeclass before dropping user."
371               */
372               raiserror 18554, @name_in_db, @sp_name
373           end
374           return (1)
375       end
376   
377       /*
378       ** Check if userid appears in sysprocesses. In that case somebody
379       ** is still using it so we can not remove it.
380       */
381       if exists (select * from master.dbo.sysprocesses where uid = @uid
382                   and dbid = db_id())
383       begin
384           /*
385           ** 17915, "Warning: the specified account is currently active."
386           */
387           exec sp_getmessage 17915, @msg output
388           print @msg
389   
390           /*
391           ** 17918, "Nothing changed."
392           */
393           exec sp_getmessage 17918, @msg output
394           print @msg
395           return (1)
396       end
397   
398       /*
399       **  Drop the user.
400       **  Also drop any references to the user in the sysprotects table.
401       **  If anyone is aliased to the user, drop them also.
402       **
403       ** IMPORTANT: The name rs_logexec is significant. It is used by 
404       ** Replication Server.
405       */
406   
407   
408   
409       begin transaction rs_logexec
410   
411   
412   
413       delete from sysusers
414       where suid = @suid
415       select @err1 = @@error, @userdropped = @@rowcount
416   
417   
418   
419       delete from sysprotects
420       where uid = @uid
421   
422   
423   
424       /*
425       **  Drop any dependent aliases.
426       */
427       if exists (select *
428               from sysalternates
429               where altsuid = @suid)
430       begin
431           delete from sysalternates
432           where altsuid = @suid
433   
434   
435           /*
436           ** 17547, "The dependent aliases were also dropped."
437           */
438           exec sp_getmessage 17547, @msg output
439           print @msg
440       end
441   
442       /* 
443       **  Delete entries from sysattributes
444       */
445       delete from sysattributes
446       where object_type = "U"
447           and object = @uid
448   
449   
450   
451       /* Delete entries from sysencryptkeys */
452   
453       if exists (select * from sysencryptkeys
454               where uid = @uid)
455       begin
456           delete from sysencryptkeys
457           where uid = @uid
458       end
459   
460   
461   
462       if @userdropped != 1
463       begin
464           /*
465           ** 19941, "You cannot drop the user because the suid value for
466           ** user '%1!' is not unique in the database."
467           */
468           raiserror 19941, @name_in_db
469   
470           goto clean_all
471       end
472   
473       /*
474       ** Write the log record to replicate this invocation 
475       ** of the stored procedure.
476       */
477       if (logexec() != 1)
478       begin
479           /*
480           ** 17756, "The execution of the stored procedure '%1!'
481           ** 	   in database '%2!' was aborted because there
482           ** 	   was an error in writing the replication log
483           **	   record."
484           */
485           select @dbname = db_name()
486           raiserror 17756, "sp_dropuser", @dbname
487   
488           rollback transaction rs_logexec
489           return (1)
490       end
491   
492   
493   
494       commit transaction rs_logexec
495   
496       /* Update protection timestamp in Resource */
497       grant all to null
498   
499   print_msg:
500   
501       /*
502       ** 17548, "User has been dropped from current database."
503       */
504       exec sp_getmessage 17548, @msg output
505       print @msg
506   
507       return (0)
508   
509   clean_all:
510       rollback transaction rs_logexec
511       return (1)
512   


exec sp_procxmode 'sp_dropuser', 'AnyMode'
go

Grant Execute on sp_dropuser to public
go
RESULT SETS
sp_dropuser_rset_005
sp_dropuser_rset_004
sp_dropuser_rset_003
sp_dropuser_rset_002
sp_dropuser_rset_001

DEFECTS
 QJWI 5 Join or Sarg Without Index 219
 QJWI 5 Join or Sarg Without Index 252
 QJWI 5 Join or Sarg Without Index 289
 QJWI 5 Join or Sarg Without Index 304
 QJWI 5 Join or Sarg Without Index 360
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysencryptkeys sybsystemprocs..sysencryptkeys
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysprotects sybsystemprocs..sysprotects
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
185
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
212
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {grantor}
218
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
220
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
240
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
333
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {protecttype, uid}
347
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {uid}
420
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object}
446
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysencryptkeys.csysencryptkeys unique clustered
(id, type, uid)
Intersection: {uid}
454
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysencryptkeys.csysencryptkeys unique clustered
(id, type, uid)
Intersection: {uid}
457
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 188
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 227
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 253
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 290
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 331
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 332
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 347
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 366
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 382
 TNOI 4 Table with no index master..sysprocesses master..sysprocesses
 TNOI 4 Table with no index sybsystemprocs..syssegments sybsystemprocs..syssegments
 TNOI 4 Table with no index sybsystemprocs..systhresholds sybsystemprocs..systhresholds
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 294
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 295
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 304
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 309
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 310
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysprocesses  
 MGTP 3 Grant to public sybsystemprocs..sp_dropuser  
 MGTP 3 Grant to public sybsystemprocs..sysalternates  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysprotects  
 MGTP 3 Grant to public sybsystemprocs..syssegments  
 MGTP 3 Grant to public sybsystemprocs..systhresholds  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check return value of exec 263
 MNER 3 No Error Check should check return value of exec 264
 MNER 3 No Error Check should check return value of exec 266
 MNER 3 No Error Check should check @@error after insert 278
 MNER 3 No Error Check should check @@error after insert 283
 MNER 3 No Error Check should check @@error after insert 298
 MNER 3 No Error Check should check return value of exec 341
 MNER 3 No Error Check should check return value of exec 387
 MNER 3 No Error Check should check return value of exec 393
 MNER 3 No Error Check should check @@error after delete 419
 MNER 3 No Error Check should check @@error after delete 431
 MNER 3 No Error Check should check return value of exec 438
 MNER 3 No Error Check should check @@error after delete 445
 MNER 3 No Error Check should check @@error after delete 456
 MNER 3 No Error Check should check return value of exec 504
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 128
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 142
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 177
 MUCO 3 Useless Code Useless Brackets 197
 MUCO 3 Useless Code Useless Brackets 203
 MUCO 3 Useless Code Useless Brackets 227
 MUCO 3 Useless Code Useless Brackets 366
 MUCO 3 Useless Code Useless Brackets 374
 MUCO 3 Useless Code Useless Brackets 395
 MUCO 3 Useless Code Useless Brackets 477
 MUCO 3 Useless Code Useless Brackets 489
 MUCO 3 Useless Code Useless Brackets 507
 MUCO 3 Useless Code Useless Brackets 511
 MUIN 3 Column created using implicit nullability 271
 MUIN 3 Column created using implicit nullability 276
 QAFM 3 Var Assignment from potentially many rows 183
 QAFM 3 Var Assignment from potentially many rows 365
 QCRS 3 Conditional Result Set 238
 QCRS 3 Conditional Result Set 249
 QCRS 3 Conditional Result Set 324
 QCRS 3 Conditional Result Set 345
 QCRS 3 Conditional Result Set 357
 QCTC 3 Conditional Table Creation 271
 QCTC 3 Conditional Table Creation 276
 QDIS 3 Check correct use of 'select distinct' 285
 QDIS 3 Check correct use of 'select distinct' 300
 QDIS 3 Check correct use of 'select distinct' 345
 QGWO 3 Group by/Distinct/Union without order by 285
 QGWO 3 Group by/Distinct/Union without order by 300
 QISO 3 Set isolation level 121
 QJWT 3 Join or Sarg Without Index on temp table 290
 QJWT 3 Join or Sarg Without Index on temp table 331
 QJWT 3 Join or Sarg Without Index on temp table 332
 QNAJ 3 Not using ANSI Inner Join 250
 QNAJ 3 Not using ANSI Inner Join 288
 QNAJ 3 Not using ANSI Inner Join 303
 QNAJ 3 Not using ANSI Inner Join 329
 QNAJ 3 Not using ANSI Inner Join 346
 QNAJ 3 Not using ANSI Inner Join 358
 QNUA 3 Should use Alias: Table #sysprotects2 290
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_type, object_info1}
227
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_type, object_info1}
366
 QSWV 3 Sarg with variable @suid, Candidate Index: sysusers.csysusers clustered(suid) F 414
 VNRD 3 Variable is not read @dummy 143
 VNRD 3 Variable is not read @msg_true 263
 VNRD 3 Variable is not read @msg_false 264
 VNRD 3 Variable is not read @err1 415
 MRST 2 Result Set Marker 238
 MRST 2 Result Set Marker 249
 MRST 2 Result Set Marker 324
 MRST 2 Result Set Marker 345
 MRST 2 Result Set Marker 357
 MSUB 2 Subquery Marker 187
 MSUB 2 Subquery Marker 219
 MSUB 2 Subquery Marker 381
 MSUB 2 Subquery Marker 427
 MSUB 2 Subquery Marker 453
 MSUC 2 Correlated Subquery Marker 290
 MTR1 2 Metrics: Comments Ratio Comments: 48% 80
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 34 = 40dec - 8exi + 2 80
 MTR3 2 Metrics: Query Complexity Complexity: 252 80
 PRED_QUERY_COLLECTION 2 {t=sybsystemprocs..systypes, t2=sybsystemprocs..systypes} 0 249
 PRED_QUERY_COLLECTION 2 {p=sybsystemprocs..sysprotects, u=sybsystemprocs..sysusers} 0 345
 PRED_QUERY_COLLECTION 2 {s=sybsystemprocs..syssegments, t=sybsystemprocs..systhresholds} 0 357

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..syssegments  
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)  
read_writes table sybsystemprocs..sysencryptkeys  
reads table master..spt_values (1)  
read_writes table sybsystemprocs..sysalternates  
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..syscolumns  
reads table master..sysprocesses (1)  
reads table sybsystemprocs..systypes  
read_writes table tempdb..#sysprotects2 (1) 
read_writes table sybsystemprocs..sysusers  
reads table master..sysdatabases (1)  
read_writes table sybsystemprocs..sysattributes  
read_writes table tempdb..#sysprotects1 (1) 
read_writes table sybsystemprocs..sysprotects  
reads table sybsystemprocs..systhresholds  
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)