1
2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3 /* 4.8 1.1 06/14/90 sproc/src/help */
4 /*
5 ** Messages for "sp_helprotect" 17673
6 **
7 ** 17431, "true"
8 ** 17432, "false"
9 ** 17460, "Object must be in the current database."
10 ** 17232, "No user with the specified name exists in the current database."
11 ** 17673, "All"
12 ** 17674, "No such object or user exists in the database."
13 ** 17675, "Illegal string found where the keyword grant is expected."
14 ** 17676, "This may be a temporary object. Please execute procedure from your
15 ** temporary database."
16 ** 17677, "sp_helprotect [name [, username [, 'grant' [,'none'|'granted'|'enabled'|role_name [,permission_name]]]]]"
17 ** 17678, "Illegal role name '%1!' specified."
18 ** 18349, "@rolename = 'enabled' option is allowed only for the current user."
19 ** 18350, "An error was encountered in processing the group-level permissions."
20 ** 18351, "An error was encountered in processing the user-level permissions."
21 ** 18900, "Implicit grant to public for SQLJ functions."
22 ** 19610, "%1! is an invalid permission name."
23 */
24 create procedure sp_helprotect
25 @name varchar(767) = NULL, /* name of object or user to check */
26 @username varchar(255) = NULL, /* name of user to restrict check */
27 @option varchar(10) = NULL, /* keyword grant implies with grant option */
28 @rolename varchar(255) = NULL, /* the role to be taken into account when
29 ** calculating the permissions. Can be set to
30 ** "granted", "enabled", "none" , NULL or a
31 ** specific rolename
32 */
33 @permission_name varchar(30) = NULL /* name of permission to
34 ** restrict check
35 */
36 as
37
38 declare @low int /* range of userids to check */
39 declare @high int
40 declare @olow int /* range of objectids to check */
41 declare @ohigh int
42 declare @invalid_uid int
43 declare @objid int /* id of @name if object */
44 declare @msg varchar(1024)
45 declare @msg_true varchar(9) /* msg for "true" equivalent */
46 declare @msg_false varchar(9) /* msg for "false" equivalent */
47 declare @is_user int /* whether name specified is a username */
48 declare @user_suid int /* user's suid if a username is
49 ** specified
50 */
51 declare @attr_udr_class int /* value of UDR_CLASS in sysattributes */
52 declare @attr_udr_role_hierarchy int /* value of attribute in sysattributes
53 ** for rows corresponding to role
54 ** hierarchy
55 */
56 declare @cur_id int /* for holding object_id from cursor fetch */
57 declare @cur_uid int /* for holding uid from cursor fetch */
58 declare @cur_action smallint /* for holding action from cursor fetch */
59 declare @cur_protype tinyint /* for holding protection type from fetch */
60 declare @cur_grantor int /* for holding grantor from cursor fetch */
61 declare @cur_number int /* for holding column number from cursor fetch*/
62 declare @group_name varchar(255) /* group that the user belongs to */
63 declare @tmpstr varchar(255) /* rolename */
64 declare @rolelist varchar(255) /* list of roles from show_role() */
65 declare @maxsysrolenum int
66 declare @permission_number int
67 declare @cmd varchar(3000)
68 declare @dbcc_action int
69 declare @lowercase_permission_name varchar(30)
70
71 if @@trancount = 0
72 begin
73 set chained off
74 end
75
76 set transaction isolation level 1
77 select @attr_udr_class = class
78 from master.dbo.sysattributes
79 where object_type = "UR"
80
81 select @maxsysrolenum = 31
82
83 select @attr_udr_role_hierarchy = 2
84 select @is_user = 0
85 select @permission_number = 0
86 /*
87 ** dbcc_action used to distinguish between actions
88 ** that use pseudo object ids
89 */
90 select @dbcc_action = 317
91
92 /*
93 ** Check to see that the object names are local to the current database.
94 */
95 if @name like "%.%.%" and
96 substring(@name, 1, charindex(".", @name) - 1) != db_name()
97 begin
98 /* 17460, "Object must be in the current database." */
99 raiserror 17460
100 return (1)
101 end
102
103 /* If this is a temporary table; object does not belong to
104 ** this database; (we should be in our temporary database)
105 */
106 if (@name like "#%" and db_name() != db_name(tempdb_id()))
107 begin
108 /*
109 ** 17676, "This may be a temporary object. Please execute
110 ** procedure from your temporary database."
111 */
112 raiserror 17676
113 return (1)
114 end
115
116 /*
117 ** Check if the grant keyword is correct
118 */
119 if (@option is not null and lower(@option) != "grant")
120 begin
121 /*
122 ** 17675, "Illegal string found where the keyword grant is expected."
123 */
124 raiserror 17675
125 return (1)
126 end
127
128 /* Trim blanks for rolename */
129 select @tmpstr = lower(ltrim(rtrim(@rolename)))
130
131 /*
132 ** Check if the rolename parameter is correct
133 */
134 if (@rolename is not NULL) and
135 (@tmpstr != "granted") and
136 (@tmpstr != "enabled") and
137 (@tmpstr != "none") and
138 (role_id(@rolename) is NULL)
139 begin
140 /*
141 ** 17678, "Illegal rolename '%1!' specified"
142 */
143 raiserror 17678, @rolename
144 return (1)
145 end
146
147
148
149 /*
150 ** Initialize the range of user ids to check. We will either check all
151 ** users (the initial values) or just one in particular (@low = @high)
152 ** NOTE: @low has been set to @@minuserid - 1 because the valid uid range is
153 ** @@minuserid to @@maxuserid.
154 ** For objects set the range from -MAXINT to MAXINT. There are no
155 ** global variables in the server to represent that, so use @@maxuserid.
156 */
157 select @invalid_uid = @@minuserid - 1 /* uid outside -ve range */
158
159 select @low = @invalid_uid, @high = @@maxuserid
160
161 /*
162 ** Initialize the range of object ids to include. There are
163 ** some special negative object ids (such as the object id which
164 ** represents execution of a DBCC command), so the range extends
165 ** across the entire set of +ve and -ve integer values.
166 */
167 select @olow = - @@maxuserid, @ohigh = @@maxuserid
168
169
170 /*
171 ** There are two cases handled by this procedure. If the first parameter
172 ** is an object (table, view, procedure) then @name is taken as an object
173 ** name and the procedure will figure out permissions for the object.
174 **
175 ** If the first parameter is not one of the objects mentioned it will be
176 ** taken as user name and all the permissions for the user or group name
177 ** will be shown.
178 */
179
180 if @name is not NULL
181 begin
182
183 /*
184 ** Check to see if it's an object.
185 */
186 if exists (select *
187 from sysobjects
188 where id = object_id(@name)
189 and (sysstat & 15 = 1 /* system table */
190 or sysstat & 15 = 2 /* view */
191 or sysstat & 15 = 3 /* user table */
192 or sysstat & 15 = 4 /* procedure */
193 or sysstat & 15 = 10 /* SQL UDF */
194 or sysstat & 15 = 15)) /* encryption key */
195 begin
196 /*
197 ** Set @olow and @ohigh so the search will be restricted to
198 ** a particular object.
199 */
200 select @olow = id, @ohigh = id
201 from sysobjects
202 where id = object_id(@name)
203 end
204
205 /* If this is a sqlj function then advise the user that the
206 ** permission is implicitly public.
207 */
208 else if exists (select *
209 from sysobjects
210 where id = object_id(@name)
211 and sysstat & 15 = 12) /*sqlj function*/
212 begin
213 /*
214 ** 18900, "Implicit grant to public for SQLJ functions."
215 */
216 execute sp_getmessage 18900, @msg output
217 print @msg
218 return (1)
219 end
220
221 else
222 begin
223 /*
224 ** Since @name is not an object let's try it as a user.
225 */
226
227 /*
228 ** Set @low and @high so the search will be restricted to
229 ** a particular user.
230 */
231 select @low = uid, @high = uid from sysusers
232 where name = @name
233 /*
234 ** Now check if it is a role.
235 ** If not, then return.
236 */
237 if (role_id(@name) is not NULL)
238 begin
239 /*
240 ** It is possible the role does not
241 ** exist in sysusers yet. Setting @high to
242 ** @invalid_uid will restrict the search
243 ** in sysusers to zero rows.
244 */
245 if (@low = @invalid_uid)
246 begin
247 select @high = @low
248 end
249
250 /* Assume it is only a role */
251 select @rolename = @name
252 select @name = NULL
253 select @username = NULL
254 select @option = NULL
255 end
256
257 else if (@low = @invalid_uid)
258 begin
259 /*
260 ** 17674, "No such object or user exists in the
261 ** database."
262 */
263 raiserror 17674
264 return (1)
265 end
266 end
267 end
268
269 if @username is not NULL
270 begin
271 /* Re-initialize @low to validate @username */
272 select @low = @invalid_uid
273
274 select @low = uid from sysusers where name = @username
275 /*
276 ** @username isn't valid.
277 */
278 if @low = @invalid_uid
279 begin
280 /* 17232, "No user with the specified name exists in the current database." */
281 raiserror 17232
282 return (1)
283 end
284 else
285 begin
286 /*
287 ** If we already have a userid in @high, then they
288 ** specified a user in @name as well. The two
289 ** had better be the same.
290 */
291 if @high != @@maxuserid
292 begin
293 if @low != @high
294 begin
295 /*
296 ** 17677, "sp_helprotect [name [, username
297 ** [, 'grant' [,'none'|'granted'|'enabled'|
298 ** role_name [,permission_name]]]]]"
299 */
300 raiserror 17677
301 return (1)
302 end
303 end
304 else
305 begin
306 select @high = @low
307 end
308 end
309 end
310
311 if @permission_name is not NULL
312 begin
313 select @lowercase_permission_name = lower(@permission_name)
314 if exists (select * from master.dbo.spt_values
315 where lower(name) = @lowercase_permission_name and type = "T")
316 begin
317 select @permission_number = number from master.dbo.spt_values
318 where lower(name) = @lowercase_permission_name and type = "T"
319 end
320 else
321 begin
322 /*
323 ** 19610, "%1! is an invalid permission name."
324 */
325 raiserror 19610, @permission_name
326 return (1)
327 end
328 end
329
330 /*
331 ** Check if this is for a specific user, role or group. If this is for a
332 ** specific user, @high will be set to uid of that user instead of @@maxuserid.
333 */
334 if @high != @@maxuserid
335 begin
336
337 /* check if this is for a specific user */
338 if exists (select * from sysusers where uid = @high and uid != gid)
339 begin
340 select @is_user = 1
341 select @user_suid = suid from sysusers where uid = @high
342 end
343 else
344 select @is_user = 0
345 end
346
347 set nocount on
348
349 create table #sysprotects1(id int, uid int, action smallint,
350 protecttype tinyint, grantor int, number int)
351
352 create table #sysprotects2(id int, col_count int)
353
354 insert into #sysprotects2(id, col_count)
355 select id, count(*) from syscolumns group by id
356
357 /*
358 ** If the permissions are asked for a user, the logic below takes
359 ** the roles and groups also into account when calculating the permissions.
360 ** The way the roles are to be taken into account depends on the value of
361 ** the rolename parameter which can have following different values:
362 ** rolename = "none" - roles are completly ignored
363 ** rolename = "granted" - all roles granted to the user are taken into account
364 ** rolename = "enabled" - only enabled roles are taken into account -
365 ** this works only for the current user.
366 ** rolename = - only the specified role is taken into account
367 **
368 ** As far as the groups are concerned, the group's permissions are always
369 ** taken into account when computing the final permission.
370 **
371 ** The logic used to implement this is as follows:
372 ** part1 - get all the roles and groups that are to be considered
373 ** for computing the permissions for the user.
374 ** part2 - compute the permissions for all the roles and put them in
375 ** one table, compute the permissions for all the groups and
376 ** put them in another table and compute the permissions for
377 ** the individual user itself and put them in yet another table.
378 ** part3 - Compute the effective permissions for the user using the
379 ** permissions table created for the roles, groups and the user.
380 */
381
382 if (@is_user = 1)
383 begin
384 /* to compute the individual user's permissions, the roles possessed
385 ** by the user and the groups that the user belongs to must also be
386 ** taken into account.
387 */
388
389 /*
390 ** Part 1: get all the roles and groups that are to be considered
391 ** for computing the permissions for the user.
392 */
393
394 create table #rolenames(name varchar(255))
395
396 /*
397 ** step1: parse the rolenames parameter
398 */
399
400 /*
401 ** allow rolename = "enabled" only for current user
402 */
403 if (@rolename = "enabled" and @user_suid != suser_id())
404 begin
405 /*
406 ** 18349, "@rolename = 'enabled' option is allowed only for
407 ** the current user."
408 */
409 raiserror 18349
410 return (1)
411 end
412
413 /*
414 ** set the rolename parameter appropriately if not specified
415 */
416 if (@rolename is NULL)
417 begin
418 /*
419 ** for current user the default corresponds to all
420 ** enabled roles, wheras for other user the default
421 ** corresponds to all granted roles
422 */
423 if (@user_suid = suser_id())
424 select @rolename = "enabled"
425 else
426 select @rolename = "granted"
427 end
428
429 /*
430 ** since role hierarchies are to be flattened at this point,
431 ** first create an intermediate temporary table to hold all
432 ** the first-level roles
433 */
434 create table #intmdt_role_set(name varchar(255))
435 /*
436 ** Create another table to store the list of granted user-defined
437 ** roles; we will call proc_role() on these.
438 */
439 create table #intmdt2_role_set(name varchar(255))
440 /*
441 ** step2: depending on the value of @rolename, populate
442 ** the rolenames table
443 */
444 if (@rolename = "granted" or @rolename = "enabled")
445 begin
446
447 /*
448 ** If the option is set to "enabled", put all the roles that
449 ** are currently granted to the user and enabled by the user
450 ** into the temporary table.
451 */
452 if (@rolename = "enabled")
453 begin
454 /* Start by getting system roles */
455 select @rolelist = show_role()
456 insert into #intmdt_role_set
457 select r.name from master.dbo.syssrvroles r,
458 master.dbo.sysloginroles l
459 where l.suid = @user_suid
460 and l.srid = r.srid
461 and charindex(r.name, @rolelist) > 0
462 /*
463 ** Get the user-defined roles for this user. We will
464 ** then call proc_role() on these to determine which
465 ** are active.
466 */
467 insert into #intmdt2_role_set
468 select r.name from master.dbo.syssrvroles r,
469 master.dbo.sysloginroles l
470 where l.suid = @user_suid
471 and l.srid = r.srid
472 and r.srid > @maxsysrolenum
473 /*
474 ** Now do the call to proc_role() to insert active
475 ** user-defined roles into #intmdt_role_set
476 */
477 insert into #intmdt_role_set
478 select i.name from #intmdt2_role_set i
479 where proc_role(i.name) = 1
480 end
481 /*
482 ** Otherwise, put all the roles that are granted to the
483 ** user into the temporary table
484 */
485 else
486 insert into #intmdt_role_set
487 select r.name from master.dbo.syssrvroles r,
488 master.dbo.sysloginroles l
489 where l.suid = @user_suid
490 and l.srid = r.srid
491
492 end
493 else if @rolename != "none"
494 begin
495 /* The rolename parameter refers to a specific role */
496 insert into #intmdt_role_set values (@rolename)
497 end
498
499 /* expand the rolenames table if required */
500 if exists (select * from #intmdt_role_set)
501 begin
502 /*
503 ** to be able to expand the first-level role set into
504 ** the expanded role set, we need two more temporary tables
505 */
506 create table #cum_role_set(name varchar(255))
507 create table #temp_role_set(name varchar(255))
508
509 /*
510 ** The following while loop does the flattening of the role
511 ** hierarchy tree corresponding to the first level roles
512 ** contained in #indmdt_role_set. The processing in the loop
513 ** goes as follows:
514 **
515 ** the start conditions are:
516 ** #intmdt_role_set contains the base set of roles
517 ** #cum_role_set is empty
518 ** #temp_role_set is empty
519 ** the loop processing is:
520 ** - select the roles that are contained by the roles
521 ** in #intmdt_role_set and put them in #temp_role_set.
522 ** - copy roles from #intmdt_role_set into #cum_role_set
523 ** if they are not already there in #cum_role_set.
524 ** - move roles from #temp_role_set into #intmdt_role_set.
525 ** the loop exit condition is:
526 ** #intmdt_role_set becomes empty.
527 ** at that point, #cum_role_set contains the expanded
528 ** list of roles
529 */
530
531 while exists (select * from #intmdt_role_set)
532 begin
533 /*
534 ** select the roles that are contained by the roles
535 ** in #intmdt_role_set and put them in #temp_role_set.
536 ** Note that for the role-hierarchy type rows in
537 ** sysattributes, the object field contains the roleid
538 ** of the parent role and the object_info1 field
539 ** contains the roleid of the child role.
540 */
541 insert into #temp_role_set
542 select role_name(object_info1)
543 from master.dbo.sysattributes a,
544 #intmdt_role_set b
545 where a.class = @attr_udr_class
546 and a.attribute = @attr_udr_role_hierarchy
547 and a.object = role_id(b.name)
548
549 /* copy from #intmdt_role_set to #cum_role_set */
550 /* Adaptive Server has expanded all '*' elements in the following statement */ insert into #cum_role_set
551 select #intmdt_role_set.name from #intmdt_role_set
552 where name not in (select name from #cum_role_set)
553
554 delete from #intmdt_role_set
555
556 /* copy from #temp_role_set to #intmdt_role_set */
557 /* Adaptive Server has expanded all '*' elements in the following statement */ insert into #intmdt_role_set
558 select #temp_role_set.name from #temp_role_set
559
560 delete from #temp_role_set
561 end
562
563 /*
564 ** At this point, the completly flattened role set is in
565 ** #cum_role_set - copy them over to #rolenames
566 */
567 insert into #rolenames select distinct name
568 from #cum_role_set
569 end
570
571 /*
572 ** step3: populate the @groupnames table
573 */
574
575 /*
576 ** create the #groupnames table
577 */
578 create table #groupnames(name varchar(255))
579
580 /* the user is always a member of the public group */
581 insert into #groupnames values ("public")
582
583 /* if user belongs to any other group, insert that too */
584 select @group_name = user_name(gid) from sysusers
585 where suid = @user_suid
586 if @group_name is not null
587 begin
588 insert into #groupnames values (@group_name)
589 end
590
591 /*
592 ** part 2 : Generate protection data for each kind of grantee
593 */
594
595 /*
596 ** step 1: create three tables, one for each kind of grantee
597 */
598
599 create table #role_permissions(id int, uid int, action smallint,
600 protecttype tinyint, grantor int, number int)
601
602 create table #group_permissions(id int, uid int, action smallint,
603 protecttype tinyint, grantor int, number int)
604
605 create table #user_permissions(id int, uid int, action smallint,
606 protecttype tinyint, grantor int, number int)
607
608 /*
609 ** step 2: Generate the permission information for each of the
610 ** roles contained in the #roles table
611 */
612
613 insert into #role_permissions(id, uid, action, protecttype, grantor, number)
614 select distinct
615 p.id, p.uid, p.action, p.protecttype, p.grantor, c.number
616 from sysprotects p, master.dbo.spt_values c
617 where convert(tinyint, substring(isnull(p.columns, 0x1), c.low, 1))
618 & c.high != 0
619 and c.type = "P" and c.number <= 1024
620 and p.uid in (select user_id(name) from #rolenames)
621 and p.id between @olow and @ohigh
622 /*
623 ** step 3: Generate permission information for each of the groups
624 ** present in #groupnames table
625 */
626 insert into #group_permissions(id, uid, action, protecttype, grantor, number)
627 select distinct
628 p.id, p.uid, p.action, p.protecttype, p.grantor, c.number
629 from sysprotects p, master.dbo.spt_values c
630 where convert(tinyint, substring(isnull(p.columns, 0x1), c.low, 1))
631 & c.high != 0
632 and c.type = "P" and c.number <= 1024
633 and p.uid in (select user_id(name) from #groupnames)
634 and p.id between @olow and @ohigh
635
636 /*
637 ** step 4 : Generate permission information for the user himself
638 */
639 insert into #user_permissions(id, uid, action, protecttype, grantor, number)
640 select distinct
641 p.id, p.uid, p.action, p.protecttype, p.grantor, c.number
642 from sysprotects p, master.dbo.spt_values c
643 where convert(tinyint, substring(isnull(p.columns, 0x1), c.low, 1))
644 & c.high != 0
645 and c.type = "P" and c.number <= 1024
646 and p.uid = @high
647 and p.id between @olow and @ohigh
648
649 /*
650 ** part 3: Generate the final permission information
651 **
652 ** The logic for determining the final permissions for a user,
653 ** the logic that is also used inside the server to actually decide
654 ** whether to allow a particular access to a user or not, is as
655 ** follows:
656 ** if (there is a grant row found where the grantee is one
657 ** of the roles possessed by the user)
658 ** then
659 ** the user has the access
660 ** else
661 ** if (there is a grant row found where the grantee is
662 ** the user himself)
663 ** then
664 ** the user has the access
665 ** else
666 ** if (there is a grant row found against the
667 ** group that the user belongs to)
668 ** and
669 ** (there is no revoke found against the
670 ** user himself)
671 ** then
672 ** the user has the access
673 ** else
674 ** the user does not have the access
675 ** fi
676 ** fi
677 ** fi
678 **
679 ** Note that if the access applies at column level, the logic
680 ** needs to be applied to each column of the object
681 */
682
683 /*
684 ** step1: create a table to hold the final permission information
685 */
686 create table #final_permissions(id int, uid int, action smallint,
687 protecttype tinyint, grantor int, number int)
688
689
690 /*
691 ** step2: Select all rows from $role_permissions table and put them
692 ** in #final_permissions since the roles permissions override all
693 ** other permissions.
694 */
695 /* Adaptive Server has expanded all '*' elements in the following statement */ insert into #final_permissions select #role_permissions.id, #role_permissions.uid, #role_permissions.action, #role_permissions.protecttype, #role_permissions.grantor, #role_permissions.number from #role_permissions
696
697 /*
698 ** step3 : Select all grant (or grant_w_grant) rows from the user's
699 ** permissions table and put them in #final_permissions table since
700 ** we know that there cannot be any revokes against roles (which can
701 ** override these grants), and user's permissions overrides the
702 ** group's permissions
703 */
704 /* Adaptive Server has expanded all '*' elements in the following statement */ insert into #final_permissions select #user_permissions.id, #user_permissions.uid, #user_permissions.action, #user_permissions.protecttype, #user_permissions.grantor, #user_permissions.number from #user_permissions
705 where protecttype = 0 or protecttype = 1
706
707 /*
708 ** step 4: process rows in #group_permissions table
709 */
710
711 declare group_cursor cursor for select id, uid, action,
712 protecttype, grantor, number from #group_permissions
713 where protecttype = 0 or protecttype = 1
714
715 open group_cursor
716
717 fetch group_cursor into @cur_id, @cur_uid, @cur_action,
718 @cur_protype, @cur_grantor, @cur_number
719
720 while (@@sqlstatus = 0)
721 begin
722 /* if the row is for non-column level object */
723 if not exists (select id from #sysprotects2 where id = @cur_id)
724 begin
725 /* if no revoke found in #user_permissions */
726 if not exists (select * from #user_permissions
727 where (id = @cur_id)
728 and (action = @cur_action)
729 and (protecttype = 2))
730 begin
731 insert into #final_permissions values (
732 @cur_id, @cur_uid, @cur_action,
733 @cur_protype, @cur_grantor, @cur_number)
734 end
735 end
736
737 else
738 /* if the object is a column level object */
739 begin
740 /* If all permission has been granted to the group
741 ** and some permissions have been revoked from the
742 ** user
743 */
744 if (col_name(@cur_id, @cur_number) is null
745 and exists (select * from #user_permissions
746 where id = @cur_id
747 and action = @cur_action
748 and protecttype = 2
749 and col_name(@cur_id, number) is not null))
750 begin
751 /*
752 ** for each column of the object for which
753 ** there is no revoke row in #user_permission
754 ** insert a grant row in #final_permissions
755 */
756 insert into #final_permissions
757 select @cur_id, @cur_uid, @cur_action,
758 @cur_protype, @cur_grantor, c.number
759 from master.dbo.spt_values c, #sysprotects2 p
760 where (c.type = "P")
761 and (c.number <= p.col_count)
762 and (c.number > 0)
763 and (p.id = @cur_id)
764 and c.number not in (select number from
765 #user_permissions u where
766 (u.id = @cur_id)
767 and (u.action = @cur_action)
768 and (u.protecttype = 2))
769 end
770 /* otherwise, the current row fetched by the cursor
771 ** is for a specific column - insert the current row in
772 ** #final_permissions if there is no revoke for
773 ** the specified column in #user_permissions
774 */
775 else
776 begin
777 if not exists (select * from #user_permissions
778 where id = @cur_id
779 and action = @cur_action
780 and ((number = @cur_number) or
781 col_name(@cur_id, number) is null)
782 and protecttype = 2)
783 begin
784 insert into #final_permissions values (
785 @cur_id, @cur_uid, @cur_action,
786 @cur_protype, @cur_grantor, @cur_number)
787 end
788 end
789 end /* end of processing for column-level objects */
790
791 fetch group_cursor into @cur_id, @cur_uid, @cur_action,
792 @cur_protype, @cur_grantor, @cur_number
793
794 end /* of while sqlstatus = 0 */
795
796 if (@@sqlstatus = 1)
797 begin
798 /*
799 ** 18350, "An error was encountered in processing
800 ** the group-level permissions."
801 */
802 execute sp_getmessage 18350, @msg output
803 print @msg
804 return (1)
805 end
806
807 close group_cursor
808
809 deallocate cursor group_cursor
810
811 /*
812 ** step 5: process revoke rows in #user_permissions table
813 */
814
815
816 declare user_cursor cursor for select id, uid, action,
817 protecttype, grantor, number from #user_permissions
818 where protecttype = 2
819
820 open user_cursor
821
822 fetch user_cursor into @cur_id, @cur_uid, @cur_action,
823 @cur_protype, @cur_grantor, @cur_number
824
825 while (@@sqlstatus = 0)
826 begin
827 /* if the row is for non-column level object */
828 if not exists (select id from #sysprotects2 where id = @cur_id)
829 begin
830 /* if no grant found in #role_permissions */
831 if not exists (select * from #role_permissions
832 where (id = @cur_id)
833 and (action = @cur_action)
834 and (protecttype = 0 or protecttype = 1))
835 begin
836 insert into #final_permissions values (
837 @cur_id, @cur_uid, @cur_action,
838 @cur_protype, @cur_grantor, @cur_number)
839 end
840 end
841
842 /* the object is a column level object */
843 else
844 begin
845 /*
846 ** If permission on all columns have been revoked
847 ** from the user and if some permissions have been
848 ** granted to any of the roles
849 */
850 if (col_name(@cur_id, @cur_number) is null
851 and exists (select * from #role_permissions
852 where id = @cur_id
853 and action = @cur_action
854 and (protecttype = 0 or protecttype = 1)
855 and col_name(@cur_id, number) is not null))
856 begin
857 /*
858 ** for each column of the object for which
859 ** there is no grant row in #role_permission
860 ** insert a revoke row in #final_permissions
861 */
862 insert into #final_permissions
863 select @cur_id, @cur_uid, @cur_action,
864 @cur_protype, @cur_grantor, c.number
865 from master.dbo.spt_values c, #sysprotects2 p
866 where (c.type = "P")
867 and (c.number <= p.col_count)
868 and (c.number > 0)
869 and (p.id = @cur_id)
870 and c.number not in (select number from
871 #role_permissions u where
872 (u.id = @cur_id)
873 and (u.action = @cur_action)
874 and (u.protecttype = 1 or
875 u.protecttype = 0))
876 end
877 /* otherwise, the current row fetched by the cursor
878 ** is for a specific column - insert the current row in
879 ** #final_permissions if there is no grant for
880 ** the specified column in #role_permissions
881 */
882 else
883 begin
884 if not exists (select * from #role_permissions
885 where id = @cur_id
886 and action = @cur_action
887 and ((number = @cur_number) or
888 col_name(@cur_id, number) is null)
889 and protecttype = 1 or protecttype = 0)
890 begin
891 insert into #final_permissions values (
892 @cur_id, @cur_uid, @cur_action,
893 @cur_protype, @cur_grantor, @cur_number)
894 end
895 end /* end of else for if(col_name(...)) is null */
896 end /* end of else for if(column-level object) */
897
898 fetch user_cursor into @cur_id, @cur_uid, @cur_action,
899 @cur_protype, @cur_grantor, @cur_number
900
901 end /* end of while */
902
903
904 if (@@sqlstatus = 1)
905 begin
906 /*
907 ** 18351, "An error was encountered in processing
908 ** the user-level permissions."
909 */
910 execute sp_getmessage 18351, @msg output
911 print @msg
912 return (1)
913 end
914
915 close user_cursor
916
917 deallocate cursor user_cursor
918
919 if @permission_number != 0
920 begin
921 /* Adaptive Server has expanded all '*' elements in the following statement */ insert into #sysprotects1 select #final_permissions.id, #final_permissions.uid, #final_permissions.action, #final_permissions.protecttype, #final_permissions.grantor, #final_permissions.number from #final_permissions
922 where action = @permission_number
923 end
924 else
925 begin
926 /* Adaptive Server has expanded all '*' elements in the following statement */ insert into #sysprotects1 select #final_permissions.id, #final_permissions.uid, #final_permissions.action, #final_permissions.protecttype, #final_permissions.grantor, #final_permissions.number from #final_permissions
927 end
928
929 end /* end of @is_user = 1 */
930 else
931 begin
932 select @cmd = "insert into #sysprotects1 (id, uid, action,
933 protecttype, grantor, number)
934 select distinct
935 p.id, p.uid, p.action, p.protecttype,
936 p.grantor, c.number
937 from sysprotects p, master.dbo.spt_values c
938 where (~isnull (convert(tinyint, substring(p.columns, c.low, 1)), 0) & c.high != 0
939 and c.number <= (select col_count from #sysprotects2 where id = p.id))
940 and c.type = 'P'
941 and c.number <= 1024
942 and p.columns is not null
943 and convert(tinyint, substring(p.columns, 1, 1)) & 0x1 != 0
944 and (convert(tinyint, substring(p.columns, 1, 1)) & 0xfe != 0
945 or substring(p.columns, 2, 1) is not null)
946 and p.id between @olow and @ohigh
947 and p.uid between @low and @high"
948 if @permission_number != 0
949 select @cmd = @cmd + " and p.action = @permission_number"
950 exec (@cmd)
951
952 select @cmd = "insert into #sysprotects1 (id, uid, action,
953 protecttype, grantor, number)
954 select distinct
955 p.id, p.uid, p.action, p.protecttype,
956 p.grantor, c.number
957 from sysprotects p, master.dbo.spt_values c
958 where convert(tinyint, substring(isnull(p.columns, 0x1), c.low, 1))
959 & c.high != 0
960 and c.type = 'P'
961 and c.number <= 1024
962 and (p.columns is null
963 or convert(tinyint, substring(p.columns, 1, 1)) & 0x1 = 0
964 or (convert(tinyint, substring(p.columns, 1, 1)) & 0xfe = 0
965 and substring(p.columns, 2, 1) is null))
966 and p.id between @olow and @ohigh
967 and p.uid between @low and @high"
968 if @permission_number != 0
969 select @cmd = @cmd + " and p.action = @permission_number"
970 exec (@cmd)
971 end
972
973 /*
974 ** 17431, "true"
975 ** 17432, "false"
976 */
977 exec sp_getmessage 17431, @msg_true output
978 exec sp_getmessage 17432, @msg_false output
979
980 /* 17673, "All" */
981 exec sp_getmessage 17673, @msg out
982
983 /* Collect the information about object name */
984 create table #temp_obj(id int, number int, objname varchar(30))
985 /*
986 ** The population of #temp is split into two queries to reduce the joins in
987 ** each query in order to improve performance.
988 */
989 insert into #temp_obj
990 select distinct p.id, p.number, o.name
991 from #sysprotects1 p, sysobjects o
992 where p.id = o.id
993
994 /*
995 ** #temp populates valid column names for valid column IDs.
996 ** There exist pad columns in some system tables which do not appear
997 ** in syscolumns. The #temp_obj table is populated with rows from these columns
998 ** IDs through #sysprotects1. These rows are removed in #temp.
999 **
1000 */
1001 create table #temp(id int, number int, objname varchar(30), colname varchar(30) null)
1002 insert into #temp
1003 select t.id, t.number, t.objname, c.name
1004 from #temp_obj t, syscolumns c
1005 where t.number = c.colid and t.id = c.id
1006 union
1007 select t.id, t.number, t.objname, null
1008 from #temp_obj t
1009 where t.number = 0
1010
1011 /*
1012 ** #temp is empty if the only granted permissions in sysprotects are
1013 ** non-object permissions, or permissions on dbcc commands or builtins.
1014 ** Insert a dummy row in #temp if empty. This is needed because #temp is
1015 ** used in a natural join with #sysprotects1 to display the final permissions
1016 ** output.
1017 */
1018 if not exists (select 1 from #temp)
1019 begin
1020 insert into #temp values (0, 0, "dummy", "dummy")
1021 end
1022
1023 if @option is NULL
1024 begin
1025 /*
1026 ** print all the privileges
1027 ** on normal columns.
1028 ** If there is no valid column name for
1029 ** a record in the #sysprotects1 p table
1030 ** and p.number=0, that means privilege is
1031 ** granted to all columns
1032 ** For spt_values.name like 'Create Table', 'Create Rule', ....,
1033 ** there is no object in sysobjects table. #sysprotects1 p has
1034 ** p.id = 0 and p.number = 0 for them. Pseduo objects such as
1035 ** DBCC commands have a negative object ID.
1036 */
1037
1038
1039 begin
1040 select distinct
1041 grantor = user_name(p.grantor),
1042 grantee = user_name(p.uid),
1043 type = substring(b.name, 1, 8),
1044 action = case
1045 when (p.id < 0 and p.action = @dbcc_action)
1046 then col_name(p.id, p.number)
1047 else a.name
1048 end,
1049 object = case
1050 when ((p.id < 0 and p.action = @dbcc_action)
1051 or p.id = 0)
1052 then ""
1053 when (p.id < 0 and p.action <> @dbcc_action)
1054 then col_name(p.id, p.number)
1055 else t.objname
1056 end,
1057 column = case
1058 when ((p.number = 0) and (p.id >= 0)) then @msg
1059 when (p.id < 0) then ""
1060 else t.colname
1061 end,
1062 grantable = upper(@msg_false)
1063 into #sphelprotect1rs
1064 from #sysprotects1 p, master.dbo.spt_values a,
1065 master.dbo.spt_values b, #temp t
1066 where a.type = "T"
1067 and a.number = p.action
1068 and b.type = "T"
1069 and b.number = (p.protecttype + 204)
1070 and ((p.id = 0 and p.number = 0)
1071 or (p.id = t.id and p.number = t.number)
1072 or (p.id < 0))
1073
1074 union
1075
1076 select distinct
1077 grantor = user_name(p.grantor),
1078 grantee = user_name(p.uid),
1079 type = substring(b.name, 1, 8),
1080 action = case
1081 when (p.id < 0
1082 and p.action = @dbcc_action)
1083 then col_name(p.id, p.number)
1084 else a.name
1085 end,
1086 object = case
1087 when ((p.id < 0
1088 and p.action = @dbcc_action)
1089 or p.id = 0)
1090 then ""
1091 when (p.id < 0
1092 and p.action <> @dbcc_action)
1093 then col_name(p.id, p.number)
1094 else t.objname
1095 end,
1096 column = case
1097 when ((p.number = 0) and (p.id >= 0)) then @msg
1098 when (p.id < 0) then ""
1099 else t.colname
1100 end,
1101 grantable = upper(@msg_true)
1102 from #sysprotects1 p, master.dbo.spt_values a,
1103 master.dbo.spt_values b, #temp t
1104 where a.type = "T"
1105 and a.number = p.action
1106 and b.type = "T"
1107 and b.number = (p.protecttype + 205)
1108 and p.protecttype = 0
1109 and ((p.id = 0 and p.number = 0)
1110 or (p.id = t.id and p.number = t.number)
1111 or (p.id < 0))
1112
1113 exec sp_autoformat @fulltabname = #sphelprotect1rs,
1114 @orderby = "order by grantor, grantee, type, action, object, column"
1115 drop table #sphelprotect1rs
1116 end
1117 end
1118 else
1119 begin
1120
1121 /*
1122 ** print only the grantable privileges
1123 */
1124
1125 select distinct
1126 grantor = user_name(p.grantor),
1127 grantee = user_name(p.uid),
1128 type = substring(b.name, 1, 8),
1129 action = case
1130 when (p.id < 0
1131 and p.action = @dbcc_action)
1132 then col_name(p.id, p.number)
1133 else a.name
1134 end,
1135 object = case
1136 when ((p.id < 0
1137 and p.action = @dbcc_action)
1138 or p.id = 0)
1139 then ""
1140 when (p.id < 0
1141 and p.action <> @dbcc_action)
1142 then col_name(p.id, p.number)
1143 else t.objname
1144 end,
1145 column = case
1146 when ((p.number = 0) and (p.id >= 0)) then @msg
1147 when (p.id < 0) then ""
1148 else t.colname
1149 end,
1150 grantable = upper(@msg_true)
1151 into #sphelprotect2rs
1152 from #sysprotects1 p, master.dbo.spt_values a,
1153 master.dbo.spt_values b, #temp t
1154 where a.type = "T"
1155 and a.number = p.action
1156 and b.type = "T"
1157 and b.number = (p.protecttype + 205)
1158 and p.protecttype = 0
1159 and ((p.id = 0 and p.number = 0)
1160 or (p.id = t.id and p.number = t.number)
1161 or (p.id < 0))
1162
1163 exec sp_autoformat @fulltabname = #sphelprotect2rs,
1164 @orderby = "order by grantor, grantee, type, action, object, column"
1165 drop table #sphelprotect2rs
1166 end
1167 return (0)
1168
exec sp_procxmode 'sp_helprotect', 'AnyMode'
go
Grant Execute on sp_helprotect to public
go
| DEFECTS |
 QJWI 5 Join or Sarg Without Index |
617 |
| QJWI 5 Join or Sarg Without Index |
630 |
| QJWI 5 Join or Sarg Without Index |
643 |
 MEST 4 Empty String will be replaced by Single Space |
1052 |
| MEST 4 Empty String will be replaced by Single Space |
1059 |
| MEST 4 Empty String will be replaced by Single Space |
1090 |
| MEST 4 Empty String will be replaced by Single Space |
1098 |
| MEST 4 Empty String will be replaced by Single Space |
1139 |
| MEST 4 Empty String will be replaced by Single Space |
1147 |
| MINU 4 Unique Index with nullable columns master..sysattributes |
master..sysattributes |
| MINU 4 Unique Index with nullable columns sybsystemprocs..sysprotects |
sybsystemprocs..sysprotects |
| MTYP 4 Assignment type mismatch @rolename: varchar(255) = varchar(767) |
251 |
| MTYP 4 Assignment type mismatch objname: varchar(30) = longsysname(255) |
990 |
| MTYP 4 Assignment type mismatch colname: varchar(30) = longsysname(255) |
1003 |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname |
1113 |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname |
1163 |
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} |
79 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type} |
315 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type} |
318 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type} |
1066 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type} |
1068 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type} |
1104 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type} |
1106 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type} |
1154 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type} |
1156 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
545 |
| QTYP 4 Comparison type mismatch smallint = int |
545 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
546 |
| QTYP 4 Comparison type mismatch smallint = int |
546 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int |
705 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int |
713 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int |
729 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int |
748 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int |
768 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int |
782 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int |
818 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int |
834 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int |
854 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int |
874 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int |
875 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int |
889 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
922 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint |
1005 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1045 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1050 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1053 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint |
1067 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1082 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1088 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1092 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint |
1105 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int |
1108 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1131 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1137 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1141 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint |
1155 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int |
1158 |
| TNOU 4 Table with no unique index master..spt_values |
master..spt_values |
| TNOU 4 Table with no unique index master..sysloginroles |
master..sysloginroles |
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause group_cursor |
711 |
| CUNU 3 Cursor not updated: cursor should contain 'for read only' clause user_cursor |
816 |
| MBLI 3 Integer Value of Binary Literal is Platform Dependant |
617 |
| MBLI 3 Integer Value of Binary Literal is Platform Dependant |
630 |
| MBLI 3 Integer Value of Binary Literal is Platform Dependant |
643 |
| MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain |
24 |
| MGTP 3 Grant to public master..spt_values |
|
| MGTP 3 Grant to public master..sysattributes |
|
| MGTP 3 Grant to public master..syssrvroles |
|
| MGTP 3 Grant to public sybsystemprocs..sp_helprotect |
|
| 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..sysusers |
|
| MNEJ 3 'Not Equal' join |
338 |
| MNER 3 No Error Check should check return value of exec |
216 |
| MNER 3 No Error Check should check @@error after insert |
354 |
| MNER 3 No Error Check should check @@error after insert |
456 |
| MNER 3 No Error Check should check @@error after insert |
467 |
| MNER 3 No Error Check should check @@error after insert |
477 |
| MNER 3 No Error Check should check @@error after insert |
486 |
| MNER 3 No Error Check should check @@error after insert |
496 |
| MNER 3 No Error Check should check @@error after insert |
541 |
| MNER 3 No Error Check should check @@error after insert |
550 |
| MNER 3 No Error Check should check @@error after delete |
554 |
| MNER 3 No Error Check should check @@error after insert |
557 |
| MNER 3 No Error Check should check @@error after delete |
560 |
| MNER 3 No Error Check should check @@error after insert |
567 |
| MNER 3 No Error Check should check @@error after insert |
581 |
| MNER 3 No Error Check should check @@error after insert |
588 |
| MNER 3 No Error Check should check @@error after insert |
613 |
| MNER 3 No Error Check should check @@error after insert |
626 |
| MNER 3 No Error Check should check @@error after insert |
639 |
| MNER 3 No Error Check should check @@error after insert |
695 |
| MNER 3 No Error Check should check @@error after insert |
704 |
| MNER 3 No Error Check should check @@error after insert |
731 |
| MNER 3 No Error Check should check @@error after insert |
756 |
| MNER 3 No Error Check should check @@error after insert |
784 |
| MNER 3 No Error Check should check return value of exec |
802 |
| MNER 3 No Error Check should check @@error after insert |
836 |
| MNER 3 No Error Check should check @@error after insert |
862 |
| MNER 3 No Error Check should check @@error after insert |
891 |
| MNER 3 No Error Check should check return value of exec |
910 |
| MNER 3 No Error Check should check @@error after insert |
921 |
| MNER 3 No Error Check should check @@error after insert |
926 |
| MNER 3 No Error Check should check return value of exec |
977 |
| MNER 3 No Error Check should check return value of exec |
978 |
| MNER 3 No Error Check should check return value of exec |
981 |
| MNER 3 No Error Check should check @@error after insert |
989 |
| MNER 3 No Error Check should check @@error after insert |
1002 |
| MNER 3 No Error Check should check @@error after insert |
1020 |
| MNER 3 No Error Check should check @@error after select into |
1040 |
| MNER 3 No Error Check should check return value of exec |
1113 |
| MNER 3 No Error Check should check @@error after select into |
1125 |
| MNER 3 No Error Check should check return value of exec |
1163 |
| MUCO 3 Useless Code Useless Brackets |
100 |
| MUCO 3 Useless Code Useless Brackets |
106 |
| MUCO 3 Useless Code Useless Brackets |
113 |
| MUCO 3 Useless Code Useless Brackets |
119 |
| MUCO 3 Useless Code Useless Brackets |
125 |
| MUCO 3 Useless Code Useless Brackets |
144 |
| MUCO 3 Useless Code Useless Brackets |
218 |
| MUCO 3 Useless Code Useless Brackets |
237 |
| MUCO 3 Useless Code Useless Brackets |
245 |
| MUCO 3 Useless Code Useless Brackets |
257 |
| MUCO 3 Useless Code Useless Brackets |
264 |
| MUCO 3 Useless Code Useless Brackets |
282 |
| MUCO 3 Useless Code Useless Brackets |
301 |
| MUCO 3 Useless Code Useless Brackets |
326 |
| MUCO 3 Useless Code Useless Brackets |
382 |
| MUCO 3 Useless Code Useless Brackets |
403 |
| MUCO 3 Useless Code Useless Brackets |
410 |
| MUCO 3 Useless Code Useless Brackets |
416 |
| MUCO 3 Useless Code Useless Brackets |
423 |
| MUCO 3 Useless Code Useless Brackets |
444 |
| MUCO 3 Useless Code Useless Brackets |
452 |
| MUCO 3 Useless Code Useless Brackets |
720 |
| MUCO 3 Useless Code Useless Brackets |
744 |
| MUCO 3 Useless Code Useless Brackets |
796 |
| MUCO 3 Useless Code Useless Brackets |
804 |
| MUCO 3 Useless Code Useless Brackets |
825 |
| MUCO 3 Useless Code Useless Brackets |
850 |
| MUCO 3 Useless Code Useless Brackets |
904 |
| MUCO 3 Useless Code Useless Brackets |
912 |
| MUCO 3 Useless Code Useless Brackets |
1045 |
| MUCO 3 Useless Code Useless Brackets |
1050 |
| MUCO 3 Useless Code Useless Brackets |
1053 |
| MUCO 3 Useless Code Useless Brackets |
1058 |
| MUCO 3 Useless Code Useless Brackets |
1059 |
| MUCO 3 Useless Code Useless Brackets |
1081 |
| MUCO 3 Useless Code Useless Brackets |
1087 |
| MUCO 3 Useless Code Useless Brackets |
1091 |
| MUCO 3 Useless Code Useless Brackets |
1097 |
| MUCO 3 Useless Code Useless Brackets |
1098 |
| MUCO 3 Useless Code Useless Brackets |
1130 |
| MUCO 3 Useless Code Useless Brackets |
1136 |
| MUCO 3 Useless Code Useless Brackets |
1140 |
| MUCO 3 Useless Code Useless Brackets |
1146 |
| MUCO 3 Useless Code Useless Brackets |
1147 |
| MUCO 3 Useless Code Useless Brackets |
1167 |
| MUIN 3 Column created using implicit nullability |
349 |
| MUIN 3 Column created using implicit nullability |
352 |
| MUIN 3 Column created using implicit nullability |
394 |
| MUIN 3 Column created using implicit nullability |
434 |
| MUIN 3 Column created using implicit nullability |
439 |
| MUIN 3 Column created using implicit nullability |
506 |
| MUIN 3 Column created using implicit nullability |
507 |
| MUIN 3 Column created using implicit nullability |
578 |
| MUIN 3 Column created using implicit nullability |
599 |
| MUIN 3 Column created using implicit nullability |
602 |
| MUIN 3 Column created using implicit nullability |
605 |
| MUIN 3 Column created using implicit nullability |
686 |
| MUIN 3 Column created using implicit nullability |
984 |
| MUIN 3 Column created using implicit nullability |
1001 |
| QAFM 3 Var Assignment from potentially many rows |
77 |
| QAFM 3 Var Assignment from potentially many rows |
317 |
| QAFM 3 Var Assignment from potentially many rows |
584 |
| QCTC 3 Conditional Table Creation |
394 |
| QCTC 3 Conditional Table Creation |
434 |
| QCTC 3 Conditional Table Creation |
439 |
| QCTC 3 Conditional Table Creation |
506 |
| QCTC 3 Conditional Table Creation |
507 |
| QCTC 3 Conditional Table Creation |
578 |
| QCTC 3 Conditional Table Creation |
599 |
| QCTC 3 Conditional Table Creation |
602 |
| QCTC 3 Conditional Table Creation |
605 |
| QCTC 3 Conditional Table Creation |
686 |
| QCTC 3 Conditional Table Creation |
1040 |
| QCTC 3 Conditional Table Creation |
1125 |
| QDIS 3 Check correct use of 'select distinct' |
614 |
| QDIS 3 Check correct use of 'select distinct' |
627 |
| QDIS 3 Check correct use of 'select distinct' |
640 |
| QDIS 3 Check correct use of 'select distinct' |
990 |
| QDIS 3 Check correct use of 'select distinct' |
1040 |
| QDIS 3 Check correct use of 'select distinct' |
1076 |
| QDIS 3 Check correct use of 'select distinct' |
1125 |
| QGWO 3 Group by/Distinct/Union without order by |
567 |
| QGWO 3 Group by/Distinct/Union without order by |
614 |
| QGWO 3 Group by/Distinct/Union without order by |
627 |
| QGWO 3 Group by/Distinct/Union without order by |
640 |
| QGWO 3 Group by/Distinct/Union without order by |
990 |
| QGWO 3 Group by/Distinct/Union without order by |
1003 |
| QGWO 3 Group by/Distinct/Union without order by |
1040 |
| QGWO 3 Group by/Distinct/Union without order by |
1076 |
| QGWO 3 Group by/Distinct/Union without order by |
1125 |
| QISO 3 Set isolation level |
76 |
| QJWT 3 Join or Sarg Without Index on temp table |
547 |
| QJWT 3 Join or Sarg Without Index on temp table |
552 |
| QJWT 3 Join or Sarg Without Index on temp table |
620 |
| QJWT 3 Join or Sarg Without Index on temp table |
633 |
| QJWT 3 Join or Sarg Without Index on temp table |
761 |
| QJWT 3 Join or Sarg Without Index on temp table |
764 |
| QJWT 3 Join or Sarg Without Index on temp table |
867 |
| QJWT 3 Join or Sarg Without Index on temp table |
870 |
| QJWT 3 Join or Sarg Without Index on temp table |
992 |
| QJWT 3 Join or Sarg Without Index on temp table |
1005 |
| QJWT 3 Join or Sarg Without Index on temp table |
1067 |
| QJWT 3 Join or Sarg Without Index on temp table |
1069 |
| QJWT 3 Join or Sarg Without Index on temp table |
1071 |
| QJWT 3 Join or Sarg Without Index on temp table |
1105 |
| QJWT 3 Join or Sarg Without Index on temp table |
1107 |
| QJWT 3 Join or Sarg Without Index on temp table |
1110 |
| QJWT 3 Join or Sarg Without Index on temp table |
1155 |
| QJWT 3 Join or Sarg Without Index on temp table |
1157 |
| QJWT 3 Join or Sarg Without Index on temp table |
1160 |
| QNAJ 3 Not using ANSI Inner Join |
457 |
| QNAJ 3 Not using ANSI Inner Join |
468 |
| QNAJ 3 Not using ANSI Inner Join |
487 |
| QNAJ 3 Not using ANSI Inner Join |
543 |
| QNAJ 3 Not using ANSI Inner Join |
616 |
| QNAJ 3 Not using ANSI Inner Join |
629 |
| QNAJ 3 Not using ANSI Inner Join |
642 |
| QNAJ 3 Not using ANSI Inner Join |
759 |
| QNAJ 3 Not using ANSI Inner Join |
865 |
| QNAJ 3 Not using ANSI Inner Join |
991 |
| QNAJ 3 Not using ANSI Inner Join |
1004 |
| QNAJ 3 Not using ANSI Inner Join |
1064 |
| QNAJ 3 Not using ANSI Inner Join |
1102 |
| QNAJ 3 Not using ANSI Inner Join |
1152 |
| QNUA 3 Should use Alias: Column object_info1 should use alias a |
542 |
| QNUA 3 Should use Alias: Table #rolenames |
620 |
| QNUA 3 Should use Alias: Table #groupnames |
633 |
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, attribute} |
545 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {id} |
621 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {id} |
634 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {uid, id} |
646 |
| QSWV 3 Sarg with variable @lowercase_permission_name, Candidate Index: spt_values.spt_valuesclust clustered(number, type) U |
315 |
| QSWV 3 Sarg with variable @lowercase_permission_name, Candidate Index: spt_values.spt_valuesclust clustered(number, type) U |
318 |
| QSWV 3 Sarg with variable @user_suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) F |
459 |
| QSWV 3 Sarg with variable @user_suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) F |
470 |
| QSWV 3 Sarg with variable @maxsysrolenum, Candidate Index: syssrvroles.csyssrvroles unique clustered(srid) F |
472 |
| QSWV 3 Sarg with variable @user_suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) F |
489 |
| QSWV 3 Sarg with variable @user_suid, Candidate Index: sysusers.csysusers clustered(suid) F |
585 |
| QTJ1 3 Table only appears in inner join clause |
544 |
| QTLO 3 Top-Level OR |
705 |
| QTLO 3 Top-Level OR |
713 |
| QTLO 3 Top-Level OR |
885 |
| QUNI 3 Check Use of 'union' vs 'union all' |
1003 |
| QUNI 3 Check Use of 'union' vs 'union all' |
1040 |
| VNRD 3 Variable is not read @name |
252 |
| VUNU 3 Variable is not used @objid |
43 |
 CUPD 2 Updatable Cursor Marker (updatable by default) |
711 |
| CUPD 2 Updatable Cursor Marker (updatable by default) |
816 |
| MDYS 2 Dynamic SQL Marker |
950 |
| MDYS 2 Dynamic SQL Marker |
970 |
| MSUB 2 Subquery Marker |
186 |
| MSUB 2 Subquery Marker |
208 |
| MSUB 2 Subquery Marker |
314 |
| MSUB 2 Subquery Marker |
338 |
| MSUB 2 Subquery Marker |
723 |
| MSUB 2 Subquery Marker |
726 |
| MSUB 2 Subquery Marker |
745 |
| MSUB 2 Subquery Marker |
764 |
| MSUB 2 Subquery Marker |
777 |
| MSUB 2 Subquery Marker |
828 |
| MSUB 2 Subquery Marker |
831 |
| MSUB 2 Subquery Marker |
851 |
| MSUB 2 Subquery Marker |
870 |
| MSUB 2 Subquery Marker |
884 |
| MTR1 2 Metrics: Comments Ratio Comments: 43% |
24 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 119 = 130dec - 13exi + 2 |
24 |
| MTR3 2 Metrics: Query Complexity Complexity: 546 |
24 |
| PRED_QUERY_COLLECTION 2 {l=master..sysloginroles, s=master..syssrvroles} 0 |
457 |
| PRED_QUERY_COLLECTION 2 {l=master..sysloginroles, s=master..syssrvroles} 0 |
468 |
| PRED_QUERY_COLLECTION 2 {l=master..sysloginroles, s=master..syssrvroles} 0 |
487 |
