DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helprotect  14 déc. 14Defects Propagation Dependencies

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 or replace 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(255) = 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 @lprof_suid int /* user's login profile id if a username is 
52        ** specified 
53        */
54        declare @attr_lr_class int /* value of login profiel class 
55        ** in sysattributes 
56        */
57        declare @attr_udr_class int /* value of UDR_CLASS in sysattributes */
58        declare @attr_udr_role_hierarchy int /* value of attribute in sysattributes
59        ** for rows corresponding to role 
60        ** hierarchy
61        */
62        declare @cur_id int /* for holding object_id from cursor fetch */
63        declare @cur_uid int /* for holding uid from cursor fetch */
64        declare @cur_action smallint /* for holding action from cursor fetch */
65        declare @cur_protype tinyint /* for holding protection type from fetch */
66        declare @cur_grantor int /* for holding grantor from cursor fetch */
67        declare @cur_predid int /* for holding predid from cursor fetch */
68        declare @cur_number int /* for holding column number from cursor fetch*/
69        declare @group_name varchar(255) /* group that the user belongs to */
70        declare @tmpstr varchar(255) /* rolename */
71        declare @rolelist varchar(255) /* list of roles from show_role() */
72        declare @maxsysrolenum int
73        declare @permission_number int
74        declare @cmd varchar(3000)
75        declare @builtin_action int
76        declare @lowercase_permission_name varchar(255)
77    
78        if @@trancount = 0
79        begin
80            set chained off
81        end
82    
83        set transaction isolation level 1
84        select @attr_udr_class = class
85        from master.dbo.sysattributes
86        where object_type = "UR"
87    
88        select @attr_lr_class = class
89        from master.dbo.sysattributes
90        where object_type = "LR"
91    
92        select @maxsysrolenum = 31
93    
94        select @attr_udr_role_hierarchy = 2
95        select @is_user = 0
96        select @permission_number = 0
97    
98        select @builtin_action = 5
99    
100       /*
101       **  Check to see that the object names are local to the current database.
102       */
103       if @name like "%.%.%" and
104           substring(@name, 1, charindex(".", @name) - 1) != db_name()
105       begin
106           /* 17460, "Object must be in the current database." */
107           raiserror 17460
108           return (1)
109       end
110   
111       /*  If this is a temporary table; object does not belong to 
112       **  this database; (we should be in our temporary database)
113       */
114       if (@name like "#%" and db_name() != db_name(tempdb_id()))
115       begin
116           /* 
117           ** 17676, "This may be a temporary object. Please execute 
118           ** procedure from your temporary database."
119           */
120           raiserror 17676
121           return (1)
122       end
123   
124       /*
125       ** Check if the grant keyword is correct
126       */
127       if (@option is not null and lower(@option) != "grant")
128       begin
129           /*
130           ** 17675, "Illegal string found where the keyword grant is expected."
131           */
132           raiserror 17675
133           return (1)
134       end
135   
136       /* Trim blanks for rolename */
137       select @tmpstr = lower(ltrim(rtrim(@rolename)))
138   
139       /*
140       ** Check if the rolename parameter is correct
141       */
142       if (@rolename is not NULL) and
143           (@tmpstr != "granted") and
144           (@tmpstr != "enabled") and
145           (@tmpstr != "none") and
146           (role_id(@rolename) is NULL)
147       begin
148           /*
149           ** 17678, "Illegal rolename '%1!' specified"
150           */
151           raiserror 17678, @rolename
152           return (1)
153       end
154   
155   
156   
157       /*
158       **  Initialize the range of user ids to check.  We will either check all
159       **  users (the initial values) or just one in particular (@low = @high)
160       **  NOTE: @low has been set to @@minuserid - 1 because the valid uid range is 
161       **  @@minuserid to @@maxuserid.  
162       **  For objects set the range from -MAXINT to MAXINT. There are no
163       **  global variables in the server to represent that, so use @@maxuserid.
164       */
165       select @invalid_uid = @@minuserid - 1 /* uid outside -ve range */
166   
167       select @low = @invalid_uid, @high = @@maxuserid
168   
169       /*
170       ** Initialize the range of object ids to include. There are
171       ** some special negative object ids (such as the object id which
172       ** represents execution of a DBCC command), so the range extends
173       ** across the entire set of +ve and -ve integer values.
174       */
175       select @olow = - @@maxuserid, @ohigh = @@maxuserid
176   
177   
178       /*
179       **  There are two cases handled by this procedure.  If the first parameter
180       **  is an object (table, view, procedure) then @name is taken as an object
181       **  name and the procedure will figure out permissions for the object.
182       **
183       **  If the first parameter is not one of the objects mentioned it will be
184       **  taken as user name and all the permissions for the user or group name
185       **  will be shown.
186       */
187   
188       if @name is not NULL
189       begin
190   
191           /*
192           **  Check to see if it's an object.
193           */
194           if exists (select *
195                   from sysobjects
196                   where id = object_id(@name)
197                       and (sysstat & 15 = 1 /* system table */
198                           or sysstat & 15 = 2 /* view */
199                           or sysstat & 15 = 3 /* user table */
200                           or sysstat & 15 = 4 /* procedure */
201                           or sysstat & 15 = 10 /* SQL UDF */
202                           or sysstat & 15 = 15)) /* encryption key */
203           begin
204               /*
205               **  Set @olow and @ohigh so the search will be restricted to
206               **  a particular object.
207               */
208               select @olow = id, @ohigh = id
209               from sysobjects
210               where id = object_id(@name)
211           end
212   
213           /* If this is a sqlj function then advise the user that the 
214           ** permission is implicitly public.
215           */
216           else if exists (select *
217                   from sysobjects
218                   where id = object_id(@name)
219                       and sysstat & 15 = 12) /*sqlj function*/
220           begin
221               /*
222               ** 18900, "Implicit grant to public for SQLJ functions."
223               */
224               execute sp_getmessage 18900, @msg output
225               print @msg
226               return (1)
227           end
228   
229           else
230           begin
231               /*
232               **  Since @name is not an object let's try it as a user.
233               */
234   
235               /*
236               **  Set @low and @high so the search will be restricted to
237               **  a particular user.
238               */
239               select @low = uid, @high = uid from sysusers
240               where name = @name
241               /*
242               **  Now check if it is a role.
243               **  If not, then return.
244               */
245               if (role_id(@name) is not NULL)
246               begin
247                   /*
248                   ** It is possible the role does not
249                   ** exist in sysusers yet. Setting @high to 
250                   ** @invalid_uid will restrict the search
251                   ** in sysusers to zero rows.
252                   */
253                   if (@low = @invalid_uid)
254                   begin
255                       select @high = @low
256                   end
257   
258                   /* Assume it is only a role */
259                   select @rolename = @name
260                   select @name = NULL
261                   select @username = NULL
262                   select @option = NULL
263               end
264   
265               else if (@low = @invalid_uid)
266               begin
267                   /*
268                   ** 17674, "No such object or user exists in the 
269                   ** database."
270                   */
271                   raiserror 17674
272                   return (1)
273               end
274           end
275       end
276   
277       if @username is not NULL
278       begin
279           /* Re-initialize @low to validate @username */
280           select @low = @invalid_uid
281   
282           select @low = uid from sysusers where name = @username
283           /*
284           **  @username isn't valid.
285           */
286           if @low = @invalid_uid
287           begin
288               /* 17232, "No user with the specified name exists in the current database." */
289               raiserror 17232
290               return (1)
291           end
292           else
293           begin
294               /*
295               ** If we already have a userid in @high, then they
296               ** specified a user in @name as well.  The two
297               ** had better be the same.
298               */
299               if @high != @@maxuserid
300               begin
301                   if @low != @high
302                   begin
303                       /* 
304                       ** 17677, "sp_helprotect [name [, username
305                       ** [, 'grant' [,'none'|'granted'|'enabled'|
306                       ** role_name [,permission_name]]]]]"
307                       */
308                       raiserror 17677
309                       return (1)
310                   end
311               end
312               else
313               begin
314                   select @high = @low
315               end
316           end
317       end
318   
319       if @permission_name is not NULL
320       begin
321           select @lowercase_permission_name = lower(@permission_name)
322           if exists (select * from master.dbo.spt_values
323                   where lower(name) = @lowercase_permission_name
324                       and (type = "T" or type = "GP"))
325           begin
326               select @permission_number = number from master.dbo.spt_values
327               where lower(name) = @lowercase_permission_name
328                   and (type = "T" or type = "GP")
329           end
330           else
331           begin
332               /*
333               ** 19610, "%1! is an invalid permission name."
334               */
335               raiserror 19610, @permission_name
336               return (1)
337           end
338       end
339   
340       /* 
341       ** Check if this is for a specific user, role or group. If this is for a 
342       ** specific user, @high will be set to uid of that user instead of @@maxuserid.
343       */
344       if @high != @@maxuserid
345       begin
346   
347           /* check if this is for a specific user */
348           if exists (select * from sysusers where uid = @high and uid != gid)
349           begin
350               select @is_user = 1
351               select @user_suid = suid from sysusers where uid = @high
352               select @lprof_suid = lpid from master.dbo.syslogins
353               where suid = @user_suid
354               if (@lprof_suid is null)
355               begin
356                   /* Default login profile */
357                   select @lprof_suid = object
358                   from master.dbo.sysattributes
359                   where class = @attr_lr_class
360                       and attribute = 4
361               end
362               else if (@lprof_suid = - 1)
363               begin
364                   /* Not using any login profile */
365                   select @lprof_suid = NULL
366               end
367           end
368           else
369               select @is_user = 0
370       end
371   
372       set nocount on
373   
374       create table #sysprotects1(id int, uid int, action smallint,
375           protecttype tinyint, grantor int, predid int null, number int)
376   
377       create table #sysprotects2(id int, col_count int)
378   
379       insert into #sysprotects2(id, col_count)
380       select id, count(*) from syscolumns group by id
381   
382       /*
383       ** If the permissions are asked for a user, the logic below takes 
384       ** the roles and groups also into account when calculating the permissions.
385       ** The way the roles are to be taken into account depends on the value of
386       ** the rolename parameter which can have following different values:
387       ** 	rolename = "none" - roles are completly ignored
388       **	rolename = "granted"  - all roles granted to the user are taken into account
389       **	rolename = "enabled" - only enabled roles are taken into account -
390       **				this works only for the current user.
391       **	rolename =  - only the specified role is taken into account
392       **
393       ** As far as the groups are concerned, the group's permissions are always 
394       ** taken into account when computing the final permission.
395       **
396       ** The logic used to implement this is as follows:
397       **	part1 - get all the roles and groups, including roles through an 
398       **		associated login profile that are to be considered 
399       **		for computing the permissions for the user.
400       **	part2 - compute the permissions for all the roles and put them in
401       **		one table, compute the permissions for all the groups and
402       **		put them in another table and compute the permissions for
403       **		the individual user itself and put them in yet another table.
404       **	part3 - Compute the effective permissions for the user using the
405       **		permissions table created for the roles, groups and the user.
406       */
407   
408       if (@is_user = 1)
409       begin
410           /* to compute the individual user's permissions, the roles possessed
411           ** by the user, the groups and login profile that the user belongs to 
412           ** must also be taken into account.
413           */
414   
415           /*
416           ** Part 1: get all the roles and groups that are to be considered
417           ** for computing the permissions for the user.
418           */
419   
420           create table #rolenames(name varchar(255))
421   
422           /*
423           ** step1: parse the rolenames parameter
424           */
425   
426           /*
427           ** allow rolename = "enabled" only for current user
428           */
429           if (@rolename = "enabled" and @user_suid != suser_id())
430           begin
431               /*
432               ** 18349, "@rolename = 'enabled' option is allowed only for 
433               ** the current user."
434               */
435               raiserror 18349
436               return (1)
437           end
438   
439           /*
440           ** set the rolename parameter appropriately if not specified
441           */
442           if (@rolename is NULL)
443           begin
444               /*
445               ** for current user the default corresponds to all 
446               ** enabled roles, wheras for other user the default 
447               ** corresponds to all granted roles
448               */
449               if (@user_suid = suser_id())
450                   select @rolename = "enabled"
451               else
452                   select @rolename = "granted"
453           end
454   
455           /*
456           ** since role hierarchies are to be flattened at this point,
457           ** first create an intermediate temporary table to hold all 
458           ** the first-level roles 
459           */
460           create table #intmdt_role_set(name varchar(255))
461           /*
462           ** Create another table to store the list of granted user-defined
463           ** roles; we will call proc_role() on these.
464           */
465           create table #intmdt2_role_set(name varchar(255))
466           /*
467           ** step2: depending on the value of @rolename, populate 
468           ** the rolenames table
469           */
470           if (@rolename = "granted" or @rolename = "enabled")
471           begin
472   
473               /*
474               ** If the option is set to "enabled", put all the roles that
475               ** are currently granted to the user directly or through a 
476               ** login profile and enabled into the temporary table.
477               */
478               if (@rolename = "enabled")
479               begin
480                   /* Start by getting system roles */
481                   select @rolelist = show_role()
482                   insert into #intmdt_role_set
483                   select distinct r.name from
484                       master.dbo.syssrvroles r,
485                       master.dbo.sysloginroles l
486                   where l.suid in (@user_suid, @lprof_suid)
487                       and l.srid = r.srid
488                       and charindex(r.name, @rolelist) > 0
489                   /*
490                   ** Get the user-defined roles for this user.  We will
491                   ** then call proc_role() on these to determine which
492                   ** are active.
493                   */
494                   insert into #intmdt2_role_set
495                   select distinct r.name from
496                       master.dbo.syssrvroles r,
497                       master.dbo.sysloginroles l
498                   where l.suid in (@user_suid, @lprof_suid)
499                       and l.srid = r.srid
500                       and r.srid > @maxsysrolenum
501                   /*
502                   ** Now do the call to proc_role() to insert active
503                   ** user-defined roles into #intmdt_role_set
504                   */
505                   insert into #intmdt_role_set
506                   select i.name from #intmdt2_role_set i
507                   where proc_role(i.name) = 1
508               end
509               /*
510               ** Otherwise, put all the roles that are granted to the 
511               ** user directly or through a login profile into the temporary 
512               ** table
513               */
514               else
515                   insert into #intmdt_role_set
516                   select distinct r.name from
517                       master.dbo.syssrvroles r,
518                       master.dbo.sysloginroles l
519                   where l.suid in (@user_suid, @lprof_suid)
520                       and l.srid = r.srid
521   
522           end
523           else if @rolename != "none"
524           begin
525               /* The rolename parameter refers to a specific role */
526               insert into #intmdt_role_set values (@rolename)
527           end
528   
529           /* expand the rolenames table if required */
530           if exists (select * from #intmdt_role_set)
531           begin
532               /*
533               ** to be able to expand the first-level role set into 
534               ** the expanded role set, we need two more temporary tables
535               */
536               create table #cum_role_set(name varchar(255))
537               create table #temp_role_set(name varchar(255))
538   
539               /*
540               ** The following while loop does the flattening of the role
541               ** hierarchy tree corresponding to the first level roles
542               ** contained in #indmdt_role_set. The processing in the loop
543               ** goes as follows:
544               **
545               ** the start conditions are:
546               **	#intmdt_role_set contains the base set of roles
547               ** 	#cum_role_set is empty
548               **	#temp_role_set is empty
549               ** the loop processing is:
550               **	- select the roles that are contained by the roles
551               **	  in #intmdt_role_set and put them in #temp_role_set.
552               **	- copy roles from #intmdt_role_set into #cum_role_set
553               **	  if they are not already there in #cum_role_set.
554               **	- move roles from #temp_role_set into #intmdt_role_set.
555               ** the loop exit condition is:
556               **	#intmdt_role_set becomes empty.
557               **	at that point, #cum_role_set contains the expanded
558               **	list of roles
559               */
560   
561               while exists (select * from #intmdt_role_set)
562               begin
563                   /*
564                   ** select the roles that are contained by the roles
565                   ** in #intmdt_role_set and put them in #temp_role_set.
566                   ** Note that for the role-hierarchy type rows in
567                   ** sysattributes, the object field contains the roleid
568                   ** of the parent role and the object_info1 field 
569                   ** contains the roleid of the child role.
570                   */
571                   insert into #temp_role_set
572                   select role_name(object_info1)
573                   from master.dbo.sysattributes a,
574                       #intmdt_role_set b
575                   where a.class = @attr_udr_class
576                       and a.attribute = @attr_udr_role_hierarchy
577                       and a.object = role_id(b.name)
578   
579                   /* copy from #intmdt_role_set to #cum_role_set */
580                   /* Adaptive Server has expanded all '*' elements in the following statement */ insert into #cum_role_set
581                   select #intmdt_role_set.name from #intmdt_role_set
582                   where name not in (select name from #cum_role_set)
583   
584                   delete from #intmdt_role_set
585   
586                   /* copy from #temp_role_set to #intmdt_role_set */
587                   /* Adaptive Server has expanded all '*' elements in the following statement */ insert into #intmdt_role_set
588                   select #temp_role_set.name from #temp_role_set
589   
590                   delete from #temp_role_set
591               end
592   
593               /* 
594               ** At this point, the completly flattened role set is in
595               ** #cum_role_set - copy them over to #rolenames
596               */
597               insert into #rolenames select distinct name
598               from #cum_role_set
599           end
600   
601           /*
602           ** step3: populate the @groupnames table
603           */
604   
605           /*
606           ** create the #groupnames table
607           */
608           create table #groupnames(name varchar(255))
609   
610           /* The user is always a member of the public group */
611           insert into #groupnames values ("public")
612   
613           /* If user belongs to any other group, insert that too */
614           select @group_name = user_name(gid) from sysusers
615           where suid = @user_suid
616           if @group_name is not null
617           begin
618               insert into #groupnames values (@group_name)
619           end
620   
621           /*
622           ** part 2 : Generate protection data for each kind of grantee
623           */
624   
625           /*
626           ** step 1: Create three tables, one for each kind of grantee
627           */
628   
629           create table #role_permissions(id int, uid int, action smallint,
630               protecttype tinyint, grantor int, predid int null, number int)
631   
632           create table #group_permissions(id int, uid int, action smallint,
633               protecttype tinyint, grantor int, predid int null, number int)
634   
635           create table #user_permissions(id int, uid int, action smallint,
636               protecttype tinyint, grantor int, predid int null, number int)
637   
638           /*
639           ** step 2: Generate the permission information for each of the
640           ** roles contained in the #roles table
641           */
642   
643           insert into #role_permissions(id, uid, action, protecttype, grantor, predid, number)
644           select distinct
645               p.id, p.uid, p.action, p.protecttype, p.grantor, p.predid, c.number
646           from sysprotects p, master.dbo.spt_values c
647           where convert(tinyint, substring(isnull(p.columns, 0x1), c.low, 1))
648               & c.high != 0
649               and c.type = "P" and c.number <= 1024
650               and p.uid in (select user_id(name) from #rolenames)
651               and p.id between @olow and @ohigh
652   
653           /*
654           ** step 3: Generate permission information for each of the groups
655           ** present in #groupnames table
656           */
657           insert into #group_permissions(id, uid, action, protecttype, grantor, predid, number)
658           select distinct
659               p.id, p.uid, p.action, p.protecttype, p.grantor, p.predid, c.number
660           from sysprotects p, master.dbo.spt_values c
661           where convert(tinyint, substring(isnull(p.columns, 0x1), c.low, 1))
662               & c.high != 0
663               and c.type = "P" and c.number <= 1024
664               and p.uid in (select user_id(name) from #groupnames)
665               and p.id between @olow and @ohigh
666   
667           /*
668           ** step 4 : Generate permission information for the user himself
669           */
670           insert into #user_permissions(id, uid, action, protecttype, grantor, predid, number)
671           select distinct
672               p.id, p.uid, p.action, p.protecttype, p.grantor, p.predid, c.number
673           from sysprotects p, master.dbo.spt_values c
674           where convert(tinyint, substring(isnull(p.columns, 0x1), c.low, 1))
675               & c.high != 0
676               and c.type = "P" and c.number <= 1024
677               and p.uid = @high
678               and p.id between @olow and @ohigh
679   
680           /*
681           ** part 3: Generate the final permission information
682           **
683           ** The logic for determining the final permissions for a user,
684           ** the logic that is also used inside the server to actually decide
685           ** whether to allow a particular access to a user or not, is as
686           ** follows:
687           **	if (there is a grant row found where the grantee is one
688           **	   of the roles possessed by the user)
689           **	then
690           **		the user has the access
691           **	else
692           **		if (there is a grant row found where the grantee is 
693           **		   the user himself)
694           **		then
695           **			the user has the access
696           **		else
697           **			if (there is a grant row found against the
698           **			    group that the user belongs to)
699           **					and
700           **			    (there is no revoke found against the
701           **			     user himself)
702           **			then
703           **				the user has the access
704           **			else
705           **				the user does not have the access
706           **			fi
707           **		fi
708           **	fi
709           **
710           **	Note that if the access applies at column level, the logic
711           **	needs to be applied to each column of the object
712           */
713   
714           /*
715           ** step1: create a table to hold the final permission information
716           */
717           create table #final_permissions(id int, uid int, action smallint,
718               protecttype tinyint, grantor int, predid int null, number int)
719   
720   
721           /*
722           ** step2: Select all rows from #role_permissions table and put them
723           ** in #final_permissions since the roles permissions override all
724           ** other permissions.
725           */
726           /* 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.predid, #role_permissions.number from #role_permissions
727   
728           /*
729           ** step3 : Select all grant (or grant_w_grant) rows from the user's
730           ** permissions table and put them in #final_permissions table since 
731           ** we know that there cannot be any revokes against roles (which can
732           ** override these grants), and user's permissions overrides the
733           ** group's permissions
734           */
735           /* 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.predid, #user_permissions.number from #user_permissions
736           where protecttype = 0 or protecttype = 1
737   
738           /*
739           ** step 4: process rows in #group_permissions table
740           */
741   
742           declare group_cursor cursor for select id, uid, action,
743               protecttype, grantor, predid, number from #group_permissions
744   
745           open group_cursor
746   
747           fetch group_cursor into @cur_id, @cur_uid, @cur_action,
748               @cur_protype, @cur_grantor, @cur_predid, @cur_number
749   
750           while (@@sqlstatus = 0)
751           begin
752               /* Process revoke rows from #group_permissions */
753               if (@cur_protype = 2)
754               begin
755                   /* If the row is for non-column level object */
756                   if not exists (select id from #sysprotects2
757                           where id = @cur_id)
758                   begin
759                       /* 
760                       ** If no grant row found in #user_permissions
761                       ** and #role_permissions, revoke on the group 
762                       ** that the user belongs to will be valid
763                       */
764                       if not exists (select * from #user_permissions
765                               where id = @cur_id
766                                   and action = @cur_action
767                                   and protecttype != 2)
768                           and not exists (select * from #role_permissions
769                               where id = @cur_id
770                                   and action = @cur_action)
771                       begin
772                           insert into #final_permissions values (
773                               @cur_id, @cur_uid, @cur_action,
774                               @cur_protype, @cur_grantor,
775                               @cur_predid, @cur_number)
776                       end
777                   end
778                   else /* If the object is a column level object */
779                   begin
780                       /*
781                       ** If all permissions have been revoked from the
782                       ** group and there are some permissions granted
783                       ** to the user/role
784                       */
785                       if (col_name(@cur_id, @cur_number) is null
786                               and exists (select * from #user_permissions
787                                   where id = @cur_id
788                                       and action = @cur_action
789                                       and protecttype != 2
790                                       and col_name(@cur_id, number)
791                                       is not null)
792                               or exists (select * from #role_permissions
793                                   where id = @cur_id
794                                       and action = @cur_action
795                                       and col_name(@cur_id, number)
796                                       is not null))
797                       begin
798                           /*
799                           ** For each column of the object for
800                           ** which there is no grant row in
801                           ** #user_permissions and 
802                           ** #role_permissions
803                           ** insert a revoke row in
804                           ** #final_permissions
805                           */
806                           insert into #final_permissions
807                           select @cur_id, @cur_uid, @cur_action,
808                               @cur_protype, @cur_grantor,
809                               @cur_predid, c.number
810                           from master.dbo.spt_values c,
811                               #sysprotects2 p
812                           where c.type = "P"
813                               and c.number <= p.col_count
814                               and c.number > 0
815                               and p.id = @cur_id
816                               and c.number not in
817                                   (select number
818                                   from #user_permissions u
819                                   where u.id = @cur_id
820                                       and u.action = @cur_action
821                                       and u.protecttype = 1 or
822                                       u.protecttype = 0)
823                               and c.number not in
824                                   (select number
825                                   from #role_permissions r
826                                   where r.id = @cur_id
827                                       and r.action = @cur_action)
828                       end /* end of no perms granted to user/role */
829                       /*
830                       ** Otherwise, the current row fetched by the 
831                       ** cursor is for a specific column - insert the
832                       ** current row in #final_permissions if there is
833                       ** no grant for the specified column in 
834                       ** #user_permissions and #role_permissions
835                       */
836                       else
837                       begin
838                           if not exists (select *
839                                   from #user_permissions
840                                   where id = @cur_id
841                                       and action = @cur_action
842                                       and ((number = @cur_number)
843                                           or col_name(@cur_id, number)
844                                           is null)
845                                       and protecttype != 2)
846                               and not exists (select *
847                                   from #role_permissions
848                                   where id = @cur_id
849                                       and action = @cur_action
850                                       and ((number = @cur_number)
851                                           or col_name(@cur_id, number)
852                                           is null))
853                           begin
854                               insert into #final_permissions
855                               values (@cur_id, @cur_uid,
856                                   @cur_action, @cur_protype,
857                                   @cur_grantor, @cur_predid,
858                                   @cur_number)
859                           end
860                       end
861   
862                   end /* end of if object is a column level object */
863               end /* end of processing revoke rows for group */
864   
865               else /* Process grant rows in #group_permissions */
866               begin
867                   /* If the row is for non-column level object */
868                   if not exists (select id from #sysprotects2
869                           where id = @cur_id)
870                   begin
871                       /* 
872                       ** If no revoke found in #user_permissions and 
873                       ** #group_permissions. Public could have
874                       ** been granted permission and another group
875                       ** that the user belongs to may have been 
876                       ** revoked the same permission.
877                       */
878                       if not exists (select * from #user_permissions
879                               where (id = @cur_id)
880                                   and (action = @cur_action)
881                                   and (protecttype = 2))
882                           and not exists (select * from #group_permissions
883                               where id = @cur_id
884                                   and action = @cur_action
885                                   and protecttype = 2)
886                       begin
887                           insert into #final_permissions values (
888                               @cur_id, @cur_uid, @cur_action,
889                               @cur_protype, @cur_grantor,
890                               @cur_predid, @cur_number)
891                       end
892                   end
893   
894                   else
895                   /* If the object is a column level object */
896                   begin
897                       /* 
898                       ** If all permission has been granted to the 
899                       ** group and some permissions have been revoked
900                       ** from the user or another group
901                       */
902                       if (col_name(@cur_id, @cur_number) is null
903                               and exists (select *
904                                   from #user_permissions
905                                   where id = @cur_id
906                                       and action = @cur_action
907                                       and protecttype = 2
908                                       and col_name(@cur_id, number)
909                                       is not null)
910                               or exists (select *
911                                   from #group_permissions
912                                   where id = @cur_id
913                                       and action = @cur_action
914                                       and protecttype = 2
915                                       and col_name(@cur_id, number)
916                                       is not null))
917                       begin
918                           /* 
919                           ** For each column of the object for 
920                           ** which there is no revoke row in 
921                           ** #user_permissions or 
922                           ** #group_permissions, insert a grant 
923                           ** row in #final_permissions 
924                           */
925                           insert into #final_permissions
926                           select @cur_id, @cur_uid,
927                               @cur_action,
928                               @cur_protype,
929                               @cur_grantor,
930                               @cur_predid, c.number
931                           from master.dbo.spt_values c,
932                               #sysprotects2 p
933                           where c.type = "P"
934                               and c.number <= p.col_count
935                               and c.number > 0
936                               and p.id = @cur_id
937                               and c.number not in
938                                   (select number
939                                   from #user_permissions u
940                                   where u.id = @cur_id
941                                       and u.action = @cur_action
942                                       and u.protecttype = 2)
943                               and c.number not in
944                                   (select number
945                                   from #group_permissions g
946                                   where g.id = @cur_id
947                                       and g.action = @cur_action
948                                       and g.protecttype = 2)
949                       end
950                       /* 
951                       ** Otherwise, the current row fetched by the 
952                       ** cursor is for a specific column - insert the
953                       ** current row in #final_permissions if there is
954                       ** no revoke for the specified column in 
955                       ** #user_permissions or #group_permissions 
956                       */
957                       else
958                       begin
959                           if not exists (select *
960                                   from #user_permissions
961                                   where id = @cur_id
962                                       and action = @cur_action
963                                       and ((number = @cur_number)
964                                           or
965                                           col_name(@cur_id, number) is null)
966                                       and protecttype = 2)
967                               and not exists (select *
968                                   from #group_permissions
969                                   where id = @cur_id
970                                       and action = @cur_action
971                                       and ((number = @cur_number)
972                                           or
973                                           col_name(@cur_id, number) is null)
974                                       and protecttype = 2)
975                           begin
976                               insert into #final_permissions
977                               values (@cur_id, @cur_uid,
978                                   @cur_action, @cur_protype,
979                                   @cur_grantor, @cur_predid,
980                                   @cur_number)
981                           end
982                       end
983                   end /* end of processing for column-level objects */
984               end /* end of processing for grant rows for group */
985   
986               fetch group_cursor into @cur_id, @cur_uid, @cur_action,
987                   @cur_protype, @cur_grantor, @cur_predid, @cur_number
988   
989           end /* of while sqlstatus = 0 */
990   
991           if (@@sqlstatus = 1)
992           begin
993               /*
994               ** 18350, "An error was encountered in processing 
995               ** the group-level permissions."
996               */
997               execute sp_getmessage 18350, @msg output
998               print @msg
999               return (1)
1000          end
1001  
1002          close group_cursor
1003  
1004          deallocate cursor group_cursor
1005  
1006          /*
1007          ** step 5: Process revoke rows in #user_permissions table
1008          */
1009  
1010          declare user_cursor cursor for select id, uid, action,
1011              protecttype, grantor, predid, number from #user_permissions
1012          where protecttype = 2
1013  
1014          open user_cursor
1015  
1016          fetch user_cursor into @cur_id, @cur_uid, @cur_action,
1017              @cur_protype, @cur_grantor, @cur_predid, @cur_number
1018  
1019          while (@@sqlstatus = 0)
1020          begin
1021              /* if the row is for non-column level object */
1022              if not exists (select id from #sysprotects2 where id = @cur_id)
1023              begin
1024                  /* if no grant found in #role_permissions */
1025                  if not exists (select * from #role_permissions
1026                          where id = @cur_id
1027                              and action = @cur_action)
1028                  begin
1029                      insert into #final_permissions values (
1030                          @cur_id, @cur_uid, @cur_action,
1031                          @cur_protype, @cur_grantor, @cur_predid,
1032                          @cur_number)
1033                  end
1034              end
1035  
1036              /* The object is a column level object */
1037              else
1038              begin
1039                  /* 
1040                  ** If permission on all columns have been revoked 
1041                  ** from the user and if some permissions have been
1042                  ** granted to any of the roles
1043                  */
1044                  if (col_name(@cur_id, @cur_number) is null
1045                          and exists (select * from #role_permissions
1046                              where id = @cur_id
1047                                  and action = @cur_action
1048                                  and col_name(@cur_id, number)
1049                                  is not null))
1050                  begin
1051                      /* 
1052                      ** For each column of the object for which 
1053                      ** there is no grant row in #role_permissions
1054                      ** insert a revoke row in #final_permissions
1055                      */
1056                      insert into #final_permissions
1057                      select @cur_id, @cur_uid, @cur_action,
1058                          @cur_protype, @cur_grantor, @cur_predid,
1059                          c.number
1060                      from master.dbo.spt_values c, #sysprotects2 p
1061                      where c.type = "P"
1062                          and c.number <= p.col_count
1063                          and c.number > 0
1064                          and p.id = @cur_id
1065                          and c.number not in (select number
1066                              from #role_permissions u
1067                              where u.id = @cur_id
1068                                  and u.action = @cur_action)
1069                  end
1070                  /* 
1071                  ** Otherwise, the current row fetched by the cursor
1072                  ** is for a specific column - insert the current row in 
1073                  ** #final_permissions if there is no grant for
1074                  ** the specified column in #role_permissions
1075                  */
1076                  else
1077                  begin
1078                      if not exists (select * from #role_permissions
1079                              where id = @cur_id
1080                                  and action = @cur_action
1081                                  and ((number = @cur_number) or
1082                                      col_name(@cur_id, number) is null))
1083                      begin
1084                          insert into #final_permissions values (
1085                              @cur_id, @cur_uid, @cur_action,
1086                              @cur_protype, @cur_grantor, @cur_predid,
1087                              @cur_number)
1088                      end
1089                  end /* end of else for if(col_name(...)) is null */
1090              end /* end of else for if(column-level object) */
1091  
1092              fetch user_cursor into @cur_id, @cur_uid, @cur_action,
1093                  @cur_protype, @cur_grantor, @cur_predid, @cur_number
1094  
1095          end /* end of while */
1096  
1097  
1098          if (@@sqlstatus = 1)
1099          begin
1100              /*
1101              ** 18351, "An error was encountered in processing 
1102              ** the user-level permissions."
1103              */
1104              execute sp_getmessage 18351, @msg output
1105              print @msg
1106              return (1)
1107          end
1108  
1109          close user_cursor
1110  
1111          deallocate cursor user_cursor
1112  
1113          if @permission_number != 0
1114          begin
1115              /* 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.predid, #final_permissions.number from #final_permissions
1116              where action = @permission_number
1117          end
1118          else
1119          begin
1120              /* 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.predid, #final_permissions.number from #final_permissions
1121          end
1122  
1123      end /* end of @is_user = 1 */
1124      else
1125      begin
1126          select @cmd = "insert into #sysprotects1 (id, uid, action, 
1127  	protecttype, grantor, predid, number)
1128  	select distinct
1129  		p.id, p.uid, p.action, p.protecttype,
1130  		p.grantor, p.predid, c.number
1131  		from sysprotects p, master.dbo.spt_values c
1132  		where (~isnull (convert(tinyint, substring(p.columns, c.low, 1)), 0) & c.high != 0
1133  			and c.number <= (select col_count from #sysprotects2 where id = p.id))
1134  			and c.type = 'P'
1135  			and c.number <= 1024
1136  			and p.columns is not null
1137  			and convert(tinyint, substring(p.columns, 1, 1)) & 0x1 != 0
1138  			and (convert(tinyint, substring(p.columns, 1, 1)) & 0xfe != 0
1139  				or substring(p.columns, 2, 1) is not null)
1140  			and p.id between @olow and @ohigh
1141  			and p.uid between @low and @high"
1142          if @permission_number != 0
1143              select @cmd = @cmd + " and p.action = @permission_number"
1144          exec (@cmd)
1145  
1146          select @cmd = "insert into #sysprotects1 (id, uid, action, 
1147  	protecttype, grantor, predid, number)
1148  	select distinct
1149  		p.id, p.uid, p.action, p.protecttype,
1150  		p.grantor, p.predid, c.number
1151  		from sysprotects p, master.dbo.spt_values c
1152  		where convert(tinyint, substring(isnull(p.columns, 0x1), c.low, 1))
1153  			& c.high != 0
1154  			and c.type = 'P'
1155  			and c.number <= 1024
1156  			and (p.columns is null
1157  				or convert(tinyint, substring(p.columns, 1, 1)) & 0x1 = 0
1158  				or (convert(tinyint, substring(p.columns, 1, 1)) & 0xfe = 0
1159  					and substring(p.columns, 2, 1) is null))
1160  			and p.id between @olow and @ohigh
1161  			and p.uid between @low and @high"
1162          if @permission_number != 0
1163              select @cmd = @cmd + " and p.action = @permission_number"
1164          exec (@cmd)
1165      end
1166  
1167      /*
1168      ** 17431, "true"
1169      ** 17432, "false"
1170      */
1171      exec sp_getmessage 17431, @msg_true output
1172      exec sp_getmessage 17432, @msg_false output
1173  
1174      /* 17673, "All" */
1175      exec sp_getmessage 17673, @msg out
1176  
1177      /* Collect the information about object name */
1178      create table #temp_obj(id int, number int, objname varchar(255))
1179      /* 
1180      ** The population of #temp is split into two queries to reduce the joins in 
1181      ** each query in order to improve performance.
1182      */
1183  
1184      /* If the action is not CHECKPOINT (6) DUMP DATABASE(76), 
1185      ** LOAD DATABASE(88), MOUNT DATABASE (122), OWN DATABASE (125),
1186      ** QUIESCE DATABASE (128), UNMOUNT DATABASE(143), USE DATABASE (148),
1187      ** get the object name from sysobjects.
1188      ** Otherwise, get from sysdatabases because p.id is really a
1189      ** database id.
1190      */
1191      insert into #temp_obj
1192      select distinct p.id, p.number, o.name
1193      from #sysprotects1 p, sysobjects o
1194      where p.id = o.id and p.action not in (6, 76, 88, 122, 125, 128, 143, 148)
1195  
1196      /* actions with dbname only apply to the master database */
1197      if (db_name() = 'master')
1198      begin
1199          create table #temp_obj1(id int, number int, dbname varchar(30))
1200          insert into #temp_obj1
1201          select distinct p.id, p.number, d.name
1202          from #sysprotects1 p, master.dbo.sysdatabases d
1203          where p.id = d.dbid and
1204              (p.action in (6, 76, 88, 122, 125, 128, 143, 148))
1205      end
1206  
1207      /* 
1208      ** #temp populates valid column names for valid column IDs. 
1209      ** There exist pad columns in some system tables which do not appear
1210      ** in syscolumns. The #temp_obj table is populated with rows from these columns 
1211      ** IDs through #sysprotects1. These rows are removed in #temp.
1212      ** 
1213      */
1214      create table #temp(id int, number int, objname varchar(255),
1215          colname varchar(255) null)
1216      insert into #temp
1217      select t.id, t.number, t.objname, c.name
1218      from #temp_obj t, syscolumns c
1219      where t.number = c.colid and t.id = c.id
1220      union
1221      select t.id, t.number, t.objname, null
1222      from #temp_obj t
1223      where t.number = 0
1224  
1225      /*
1226      ** #temp is empty if the only granted permissions in sysprotects are
1227      ** non-object permissions, or permissions on dbcc commands or builtins.
1228      ** Insert a dummy row in #temp if empty. This is needed because #temp is
1229      ** used in a natural join with #sysprotects1 to display the final permissions
1230      ** output.
1231      */
1232      if not exists (select 1 from #temp)
1233      begin
1234          insert into #temp values (0, 0, "dummy", "dummy")
1235      end
1236  
1237      /* 
1238      ** Populate the same infomation into #temp1 for server-level permissions
1239      ** that require a database name, such as DUMP DATABASE.
1240      */
1241      if (db_name() = 'master')
1242      begin
1243          create table #temp1(id int, number int, objname varchar(30),
1244              colname varchar(30) null)
1245  
1246          insert into #temp1(id, number, objname)
1247          select distinct t.id, t.number, t.dbname
1248          from #temp_obj1 t
1249      end
1250  
1251      if @option is NULL
1252      begin
1253          /*
1254          ** print all the privileges
1255          ** on normal columns. 
1256          ** If there is no valid column name for 
1257          ** a record in the #sysprotects1 p table
1258          ** and p.number=0, that means privilege is 
1259          ** granted to all columns
1260          ** For spt_values.name like 'Create Table', 'Create Rule', ....,
1261          ** there is no object in sysobjects table. #sysprotects1 p has
1262          ** p.id = 0 and p.number = 0 for them. Pseduo objects
1263          ** have a negative object ID.
1264          ** For builtins, "select builtin" is stored in spt_values. But 
1265          ** since "select" permission is granted on the builtin, display
1266          ** "select".
1267          */
1268  
1269          begin
1270              select distinct
1271                  grantor = user_name(p.grantor),
1272                  grantee = user_name(p.uid),
1273                  type = case
1274                      when p.protecttype = 1
1275                      then "Grant"
1276                      when p.protecttype = 2
1277                      then "Revoke"
1278                  end,
1279                  action = case
1280                      when (p.id < 0)
1281                      then col_name(p.id, p.number)
1282                      when (p.action = @builtin_action)
1283                      then "Select"
1284                      else a.name
1285                  end,
1286                  object = case
1287                      when (p.id < 0 or p.id = 0)
1288                      then ""
1289                      when (p.action = @builtin_action)
1290                      then (select c.name
1291                          from master.dbo.spt_values c
1292                          where c.number = p.id
1293                              and c.type = "BI")
1294                      else t.objname
1295                  end,
1296                  column = case
1297                      when ((p.number = 0) and (p.id >= 0)
1298                              and (p.action != @builtin_action))
1299                      then @msg
1300                      when (p.id < 0 or p.action = @builtin_action)
1301                      then ""
1302                      else t.colname
1303                  end,
1304                  predicate = case
1305                      when (p.predid is null)
1306                      then null
1307                      else object_name(p.predid)
1308                  end,
1309                  grantable = upper(@msg_false)
1310              into #sphelprotect1rs
1311              from #sysprotects1 p, master.dbo.spt_values a,
1312                  #temp t
1313              where (a.type = "T" or type = "GP")
1314                  and p.protecttype != 0
1315                  and a.number = p.action
1316                  and ((p.id = 0 and p.number = 0)
1317                      or (p.id = t.id and p.number = t.number)
1318                      or (p.id < 0)
1319                      or (p.action = @builtin_action))
1320                  and p.action not in (6, 76, 88, 122, 125, 128, 143, 148)
1321  
1322  
1323              union
1324  
1325              select distinct
1326                  grantor = user_name(p.grantor),
1327                  grantee = user_name(p.uid),
1328                  type = "Grant",
1329                  action = case
1330                      when (p.id < 0)
1331                      then col_name(p.id, p.number)
1332                      when (p.action = @builtin_action)
1333                      then "Select"
1334                      else a.name
1335                  end,
1336                  object = case
1337                      when (p.id < 0 or p.id = 0)
1338                      then ""
1339                      when (p.action = @builtin_action)
1340                      then (select c.name
1341                          from master.dbo.spt_values c
1342                          where c.number = p.id
1343                              and c.type = "BI")
1344                      else t.objname
1345                  end,
1346                  column = case
1347                      when ((p.number = 0)
1348                              and (p.id >= 0)
1349                              and (p.action != @builtin_action))
1350                      then @msg
1351                      when (p.id < 0
1352                              or p.action = @builtin_action)
1353                      then ""
1354                      else t.colname
1355                  end,
1356                  predicate = case
1357                      when (p.predid is null)
1358                      then null
1359                      else object_name(p.predid)
1360                  end,
1361                  grantable = upper(@msg_true)
1362              from #sysprotects1 p, master.dbo.spt_values a,
1363                  #temp t
1364              where (a.type = "T" or type = "GP")
1365                  and a.number = p.action
1366                  and p.protecttype = 0
1367                  and ((p.id = 0 and p.number = 0)
1368                      or (p.id = t.id and p.number = t.number)
1369                      or (p.id < 0)
1370                      or (p.action = @builtin_action))
1371                  and p.action not in (6, 76, 88, 122, 125, 128, 143, 148)
1372  
1373  
1374              /* 
1375              ** When in 'master' and there are permissions requiring 
1376              ** database name, such as DUMP DATABAE, display them.
1377              */
1378              if (db_name() = 'master')
1379              begin
1380  
1381                  insert into #sphelprotect1rs
1382                  select distinct
1383                      grantor = user_name(p.grantor),
1384                      grantee = user_name(p.uid),
1385                      type = case
1386                          when p.protecttype = 1
1387                          then "Grant"
1388                          when p.protecttype = 2
1389                          then "Revoke"
1390                      end,
1391                      action = a.name,
1392                      object = t.objname,
1393                      column = @msg,
1394                      predicate = null,
1395                      grantable = upper(@msg_false)
1396                  from #sysprotects1 p, master.dbo.spt_values a,
1397                      #temp1 t
1398                  where (a.type = "T" or type = "GP")
1399                      and p.protecttype != 0
1400                      and a.number = p.action
1401                      and p.id = t.id
1402                      and p.number = t.number
1403                      and p.action in (6, 76, 88, 122, 125, 128, 143, 148)
1404  
1405              end
1406  
1407              exec sp_autoformat @fulltabname = #sphelprotect1rs,
1408                  @orderby = "order by grantor, grantee, type, action, object, column"
1409              drop table #sphelprotect1rs
1410          end
1411      end
1412      else
1413      begin
1414  
1415          /*
1416          ** print only the grantable privileges
1417          */
1418  
1419          select distinct
1420              grantor = user_name(p.grantor),
1421              grantee = user_name(p.uid),
1422              type = "Grant",
1423              action = case
1424                  when (p.id < 0)
1425                  then col_name(p.id, p.number)
1426                  when (p.action = @builtin_action)
1427                  then "Select"
1428                  else a.name
1429              end,
1430              object = case
1431                  when (p.id < 0 or p.id = 0)
1432                  then ""
1433                  when (p.action = @builtin_action)
1434                  then (select c.name
1435                      from master.dbo.spt_values c
1436                      where c.number = p.id
1437                          and c.type = "BI")
1438                  else t.objname
1439              end,
1440              column = case
1441                  when ((p.number = 0) and (p.id >= 0)
1442                          and (p.action != @builtin_action))
1443                  then @msg
1444                  when (p.id < 0
1445                          or p.action = @builtin_action)
1446                  then ""
1447                  else t.colname
1448              end,
1449              predicate = case
1450                  when (p.predid is null)
1451                  then null
1452                  else object_name(p.predid)
1453              end,
1454              grantable = upper(@msg_true)
1455          into #sphelprotect2rs
1456          from #sysprotects1 p, master.dbo.spt_values a,
1457              #temp t
1458          where (a.type = "T" or type = "GP")
1459              and a.number = p.action
1460              and p.protecttype = 0
1461              and ((p.id = 0 and p.number = 0)
1462                  or (p.id = t.id and p.number = t.number)
1463                  or (p.id < 0)
1464                  or (p.action = @builtin_action))
1465              and p.action not in (6, 76, 88, 122, 125, 128, 143, 148)
1466  
1467          exec sp_autoformat @fulltabname = #sphelprotect2rs,
1468              @orderby = "order by grantor, grantee, type, action, object, column"
1469          drop table #sphelprotect2rs
1470      end
1471      return (0)
1472  


exec sp_procxmode 'sp_helprotect', 'AnyMode'
go

Grant Execute on sp_helprotect to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 647
 QJWI 5 Join or Sarg Without Index 661
 QJWI 5 Join or Sarg Without Index 674
 MEST 4 Empty String will be replaced by Single Space 1288
 MEST 4 Empty String will be replaced by Single Space 1301
 MEST 4 Empty String will be replaced by Single Space 1338
 MEST 4 Empty String will be replaced by Single Space 1353
 MEST 4 Empty String will be replaced by Single Space 1432
 MEST 4 Empty String will be replaced by Single Space 1446
 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) 259
 MTYP 4 Assignment type mismatch action: varchar(6) = varchar(255) 1391
 MTYP 4 Assignment type mismatch object: varchar(0) = varchar(30) 1392
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 1407
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 1467
 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}
86
 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}
90
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
323
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
327
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object}
Uncovered: [object_type, object_info1, object_info2, object_info3, object_cinfo]
577
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 359
 QTYP 4 Comparison type mismatch smallint = int 359
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 360
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 575
 QTYP 4 Comparison type mismatch smallint = int 575
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 576
 QTYP 4 Comparison type mismatch smallint = int 576
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 736
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 767
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 789
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 821
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 822
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 845
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 881
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 885
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 907
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 914
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 942
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 948
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 966
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 974
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 1012
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1116
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 1203
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 1219
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 1274
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 1276
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1282
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1289
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1298
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1300
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 1314
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 1315
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1319
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1332
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1339
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1349
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1352
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 1365
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 1366
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1370
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 1386
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 1388
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 1399
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 1400
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1426
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1433
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1442
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1445
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 1459
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 1460
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1464
 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 742
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause user_cursor 1010
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 196
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 208
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 210
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 218
 MAW1 3 Warning message on %name% tempdb..#sysprotects2.id: Warning message on #sysprotects2_crby_sybsystemprocs__sp_helprotect 379
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 380
 MAW1 3 Warning message on %name% tempdb..#role_permissions.id: Warning message on #role_permissions_crby_sybsystemprocs__sp_helprotect 643
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 645
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 651
 MAW1 3 Warning message on %name% tempdb..#group_permissions.id: Warning message on #group_permissions_crby_sybsystemprocs__sp_helprotect 657
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 659
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 665
 MAW1 3 Warning message on %name% tempdb..#user_permissions.id: Warning message on #user_permissions_crby_sybsystemprocs__sp_helprotect 670
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 672
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 678
 MAW1 3 Warning message on %name% tempdb..#role_permissions.id: Warning message on #role_permissions_crby_sybsystemprocs__sp_helprotect 726
 MAW1 3 Warning message on %name% tempdb..#user_permissions.id: Warning message on #user_permissions_crby_sybsystemprocs__sp_helprotect 735
 MAW1 3 Warning message on %name% tempdb..#group_permissions.id: Warning message on #group_permissions_crby_sybsystemprocs__sp_helprotect 742
 MAW1 3 Warning message on %name% tempdb..#sysprotects2.id: Warning message on #sysprotects2_crby_sybsystemprocs__sp_helprotect 756
 MAW1 3 Warning message on %name% tempdb..#sysprotects2.id: Warning message on #sysprotects2_crby_sybsystemprocs__sp_helprotect 757
 MAW1 3 Warning message on %name% tempdb..#user_permissions.id: Warning message on #user_permissions_crby_sybsystemprocs__sp_helprotect 765
 MAW1 3 Warning message on %name% tempdb..#role_permissions.id: Warning message on #role_permissions_crby_sybsystemprocs__sp_helprotect 769
 MAW1 3 Warning message on %name% tempdb..#user_permissions.id: Warning message on #user_permissions_crby_sybsystemprocs__sp_helprotect 787
 MAW1 3 Warning message on %name% tempdb..#role_permissions.id: Warning message on #role_permissions_crby_sybsystemprocs__sp_helprotect 793
 MAW1 3 Warning message on %name% tempdb..#sysprotects2.id: Warning message on #sysprotects2_crby_sybsystemprocs__sp_helprotect 815
 MAW1 3 Warning message on %name% tempdb..#user_permissions.id: Warning message on #user_permissions_crby_sybsystemprocs__sp_helprotect 819
 MAW1 3 Warning message on %name% tempdb..#role_permissions.id: Warning message on #role_permissions_crby_sybsystemprocs__sp_helprotect 826
 MAW1 3 Warning message on %name% tempdb..#user_permissions.id: Warning message on #user_permissions_crby_sybsystemprocs__sp_helprotect 840
 MAW1 3 Warning message on %name% tempdb..#role_permissions.id: Warning message on #role_permissions_crby_sybsystemprocs__sp_helprotect 848
 MAW1 3 Warning message on %name% tempdb..#sysprotects2.id: Warning message on #sysprotects2_crby_sybsystemprocs__sp_helprotect 868
 MAW1 3 Warning message on %name% tempdb..#sysprotects2.id: Warning message on #sysprotects2_crby_sybsystemprocs__sp_helprotect 869
 MAW1 3 Warning message on %name% tempdb..#user_permissions.id: Warning message on #user_permissions_crby_sybsystemprocs__sp_helprotect 879
 MAW1 3 Warning message on %name% tempdb..#group_permissions.id: Warning message on #group_permissions_crby_sybsystemprocs__sp_helprotect 883
 MAW1 3 Warning message on %name% tempdb..#user_permissions.id: Warning message on #user_permissions_crby_sybsystemprocs__sp_helprotect 905
 MAW1 3 Warning message on %name% tempdb..#group_permissions.id: Warning message on #group_permissions_crby_sybsystemprocs__sp_helprotect 912
 MAW1 3 Warning message on %name% tempdb..#sysprotects2.id: Warning message on #sysprotects2_crby_sybsystemprocs__sp_helprotect 936
 MAW1 3 Warning message on %name% tempdb..#user_permissions.id: Warning message on #user_permissions_crby_sybsystemprocs__sp_helprotect 940
 MAW1 3 Warning message on %name% tempdb..#group_permissions.id: Warning message on #group_permissions_crby_sybsystemprocs__sp_helprotect 946
 MAW1 3 Warning message on %name% tempdb..#user_permissions.id: Warning message on #user_permissions_crby_sybsystemprocs__sp_helprotect 961
 MAW1 3 Warning message on %name% tempdb..#group_permissions.id: Warning message on #group_permissions_crby_sybsystemprocs__sp_helprotect 969
 MAW1 3 Warning message on %name% tempdb..#user_permissions.id: Warning message on #user_permissions_crby_sybsystemprocs__sp_helprotect 1010
 MAW1 3 Warning message on %name% tempdb..#sysprotects2.id: Warning message on #sysprotects2_crby_sybsystemprocs__sp_helprotect 1022
 MAW1 3 Warning message on %name% tempdb..#role_permissions.id: Warning message on #role_permissions_crby_sybsystemprocs__sp_helprotect 1026
 MAW1 3 Warning message on %name% tempdb..#role_permissions.id: Warning message on #role_permissions_crby_sybsystemprocs__sp_helprotect 1046
 MAW1 3 Warning message on %name% tempdb..#sysprotects2.id: Warning message on #sysprotects2_crby_sybsystemprocs__sp_helprotect 1064
 MAW1 3 Warning message on %name% tempdb..#role_permissions.id: Warning message on #role_permissions_crby_sybsystemprocs__sp_helprotect 1067
 MAW1 3 Warning message on %name% tempdb..#role_permissions.id: Warning message on #role_permissions_crby_sybsystemprocs__sp_helprotect 1079
 MAW1 3 Warning message on %name% tempdb..#final_permissions.id: Warning message on #final_permissions_crby_sybsystemprocs__sp_helprotect 1115
 MAW1 3 Warning message on %name% tempdb..#final_permissions.id: Warning message on #final_permissions_crby_sybsystemprocs__sp_helprotect 1120
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1192
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 1194
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1194
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1201
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1203
 MAW1 3 Warning message on %name% tempdb..#temp_obj.id: Warning message on #temp_obj_crby_sybsystemprocs__sp_helprotect 1217
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 1219
 MAW1 3 Warning message on %name% tempdb..#temp_obj.id: Warning message on #temp_obj_crby_sybsystemprocs__sp_helprotect 1219
 MAW1 3 Warning message on %name% tempdb..#temp_obj.id: Warning message on #temp_obj_crby_sybsystemprocs__sp_helprotect 1221
 MAW1 3 Warning message on %name% tempdb..#temp1.id: Warning message on #temp1_crby_sybsystemprocs__sp_helprotect 1246
 MAW1 3 Warning message on %name% tempdb..#temp_obj1.id: Warning message on #temp_obj1_crby_sybsystemprocs__sp_helprotect 1247
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1280
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1281
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1287
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1292
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1297
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1300
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1316
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1317
 MAW1 3 Warning message on %name% tempdb..#temp.id: Warning message on #temp_crby_sybsystemprocs__sp_helprotect 1317
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1318
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1330
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1331
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1337
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1342
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1348
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1351
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1367
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1368
 MAW1 3 Warning message on %name% tempdb..#temp.id: Warning message on #temp_crby_sybsystemprocs__sp_helprotect 1368
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1369
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1401
 MAW1 3 Warning message on %name% tempdb..#temp1.id: Warning message on #temp1_crby_sybsystemprocs__sp_helprotect 1401
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1424
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1425
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1431
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1436
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1441
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1444
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1461
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1462
 MAW1 3 Warning message on %name% tempdb..#temp.id: Warning message on #temp_crby_sybsystemprocs__sp_helprotect 1462
 MAW1 3 Warning message on %name% tempdb..#sysprotects1.id: Warning message on #sysprotects1_crby_sybsystemprocs__sp_helprotect 1463
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 647
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 661
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 674
 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..sysdatabases  
 MGTP 3 Grant to public master..sysloginroles  
 MGTP 3 Grant to public master..syslogins  
 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 348
 MNER 3 No Error Check should check return value of exec 224
 MNER 3 No Error Check should check @@error after insert 379
 MNER 3 No Error Check should check @@error after insert 482
 MNER 3 No Error Check should check @@error after insert 494
 MNER 3 No Error Check should check @@error after insert 505
 MNER 3 No Error Check should check @@error after insert 515
 MNER 3 No Error Check should check @@error after insert 526
 MNER 3 No Error Check should check @@error after insert 571
 MNER 3 No Error Check should check @@error after insert 580
 MNER 3 No Error Check should check @@error after delete 584
 MNER 3 No Error Check should check @@error after insert 587
 MNER 3 No Error Check should check @@error after delete 590
 MNER 3 No Error Check should check @@error after insert 597
 MNER 3 No Error Check should check @@error after insert 611
 MNER 3 No Error Check should check @@error after insert 618
 MNER 3 No Error Check should check @@error after insert 643
 MNER 3 No Error Check should check @@error after insert 657
 MNER 3 No Error Check should check @@error after insert 670
 MNER 3 No Error Check should check @@error after insert 726
 MNER 3 No Error Check should check @@error after insert 735
 MNER 3 No Error Check should check @@error after insert 772
 MNER 3 No Error Check should check @@error after insert 806
 MNER 3 No Error Check should check @@error after insert 854
 MNER 3 No Error Check should check @@error after insert 887
 MNER 3 No Error Check should check @@error after insert 925
 MNER 3 No Error Check should check @@error after insert 976
 MNER 3 No Error Check should check return value of exec 997
 MNER 3 No Error Check should check @@error after insert 1029
 MNER 3 No Error Check should check @@error after insert 1056
 MNER 3 No Error Check should check @@error after insert 1084
 MNER 3 No Error Check should check return value of exec 1104
 MNER 3 No Error Check should check @@error after insert 1115
 MNER 3 No Error Check should check @@error after insert 1120
 MNER 3 No Error Check should check return value of exec 1171
 MNER 3 No Error Check should check return value of exec 1172
 MNER 3 No Error Check should check return value of exec 1175
 MNER 3 No Error Check should check @@error after insert 1191
 MNER 3 No Error Check should check @@error after insert 1200
 MNER 3 No Error Check should check @@error after insert 1216
 MNER 3 No Error Check should check @@error after insert 1234
 MNER 3 No Error Check should check @@error after insert 1246
 MNER 3 No Error Check should check @@error after select into 1270
 MNER 3 No Error Check should check @@error after insert 1381
 MNER 3 No Error Check should check return value of exec 1407
 MNER 3 No Error Check should check @@error after select into 1419
 MNER 3 No Error Check should check return value of exec 1467
 MUCO 3 Useless Code Useless Brackets 108
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 121
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 152
 MUCO 3 Useless Code Useless Brackets 226
 MUCO 3 Useless Code Useless Brackets 245
 MUCO 3 Useless Code Useless Brackets 253
 MUCO 3 Useless Code Useless Brackets 265
 MUCO 3 Useless Code Useless Brackets 272
 MUCO 3 Useless Code Useless Brackets 290
 MUCO 3 Useless Code Useless Brackets 309
 MUCO 3 Useless Code Useless Brackets 336
 MUCO 3 Useless Code Useless Brackets 354
 MUCO 3 Useless Code Useless Brackets 362
 MUCO 3 Useless Code Useless Brackets 408
 MUCO 3 Useless Code Useless Brackets 429
 MUCO 3 Useless Code Useless Brackets 436
 MUCO 3 Useless Code Useless Brackets 442
 MUCO 3 Useless Code Useless Brackets 449
 MUCO 3 Useless Code Useless Brackets 470
 MUCO 3 Useless Code Useless Brackets 478
 MUCO 3 Useless Code Useless Brackets 750
 MUCO 3 Useless Code Useless Brackets 753
 MUCO 3 Useless Code Useless Brackets 785
 MUCO 3 Useless Code Useless Brackets 902
 MUCO 3 Useless Code Useless Brackets 991
 MUCO 3 Useless Code Useless Brackets 999
 MUCO 3 Useless Code Useless Brackets 1019
 MUCO 3 Useless Code Useless Brackets 1044
 MUCO 3 Useless Code Useless Brackets 1098
 MUCO 3 Useless Code Useless Brackets 1106
 MUCO 3 Useless Code Useless Brackets 1197
 MUCO 3 Useless Code Useless Brackets 1241
 MUCO 3 Useless Code Useless Brackets 1280
 MUCO 3 Useless Code Useless Brackets 1282
 MUCO 3 Useless Code Useless Brackets 1287
 MUCO 3 Useless Code Useless Brackets 1289
 MUCO 3 Useless Code Useless Brackets 1297
 MUCO 3 Useless Code Useless Brackets 1300
 MUCO 3 Useless Code Useless Brackets 1305
 MUCO 3 Useless Code Useless Brackets 1330
 MUCO 3 Useless Code Useless Brackets 1332
 MUCO 3 Useless Code Useless Brackets 1337
 MUCO 3 Useless Code Useless Brackets 1339
 MUCO 3 Useless Code Useless Brackets 1347
 MUCO 3 Useless Code Useless Brackets 1351
 MUCO 3 Useless Code Useless Brackets 1357
 MUCO 3 Useless Code Useless Brackets 1378
 MUCO 3 Useless Code Useless Brackets 1424
 MUCO 3 Useless Code Useless Brackets 1426
 MUCO 3 Useless Code Useless Brackets 1431
 MUCO 3 Useless Code Useless Brackets 1433
 MUCO 3 Useless Code Useless Brackets 1441
 MUCO 3 Useless Code Useless Brackets 1444
 MUCO 3 Useless Code Useless Brackets 1450
 MUCO 3 Useless Code Useless Brackets 1471
 MUIN 3 Column created using implicit nullability 374
 MUIN 3 Column created using implicit nullability 377
 MUIN 3 Column created using implicit nullability 420
 MUIN 3 Column created using implicit nullability 460
 MUIN 3 Column created using implicit nullability 465
 MUIN 3 Column created using implicit nullability 536
 MUIN 3 Column created using implicit nullability 537
 MUIN 3 Column created using implicit nullability 608
 MUIN 3 Column created using implicit nullability 629
 MUIN 3 Column created using implicit nullability 632
 MUIN 3 Column created using implicit nullability 635
 MUIN 3 Column created using implicit nullability 717
 MUIN 3 Column created using implicit nullability 1178
 MUIN 3 Column created using implicit nullability 1199
 MUIN 3 Column created using implicit nullability 1214
 MUIN 3 Column created using implicit nullability 1243
 QAFM 3 Var Assignment from potentially many rows 84
 QAFM 3 Var Assignment from potentially many rows 88
 QAFM 3 Var Assignment from potentially many rows 326
 QAFM 3 Var Assignment from potentially many rows 357
 QCTC 3 Conditional Table Creation 420
 QCTC 3 Conditional Table Creation 460
 QCTC 3 Conditional Table Creation 465
 QCTC 3 Conditional Table Creation 536
 QCTC 3 Conditional Table Creation 537
 QCTC 3 Conditional Table Creation 608
 QCTC 3 Conditional Table Creation 629
 QCTC 3 Conditional Table Creation 632
 QCTC 3 Conditional Table Creation 635
 QCTC 3 Conditional Table Creation 717
 QCTC 3 Conditional Table Creation 1199
 QCTC 3 Conditional Table Creation 1243
 QCTC 3 Conditional Table Creation 1270
 QCTC 3 Conditional Table Creation 1419
 QDIS 3 Check correct use of 'select distinct' 483
 QDIS 3 Check correct use of 'select distinct' 495
 QDIS 3 Check correct use of 'select distinct' 516
 QDIS 3 Check correct use of 'select distinct' 644
 QDIS 3 Check correct use of 'select distinct' 658
 QDIS 3 Check correct use of 'select distinct' 671
 QDIS 3 Check correct use of 'select distinct' 1192
 QDIS 3 Check correct use of 'select distinct' 1201
 QDIS 3 Check correct use of 'select distinct' 1270
 QDIS 3 Check correct use of 'select distinct' 1325
 QDIS 3 Check correct use of 'select distinct' 1382
 QDIS 3 Check correct use of 'select distinct' 1419
 QGWO 3 Group by/Distinct/Union without order by 483
 QGWO 3 Group by/Distinct/Union without order by 495
 QGWO 3 Group by/Distinct/Union without order by 516
 QGWO 3 Group by/Distinct/Union without order by 597
 QGWO 3 Group by/Distinct/Union without order by 644
 QGWO 3 Group by/Distinct/Union without order by 658
 QGWO 3 Group by/Distinct/Union without order by 671
 QGWO 3 Group by/Distinct/Union without order by 1192
 QGWO 3 Group by/Distinct/Union without order by 1201
 QGWO 3 Group by/Distinct/Union without order by 1217
 QGWO 3 Group by/Distinct/Union without order by 1247
 QGWO 3 Group by/Distinct/Union without order by 1270
 QGWO 3 Group by/Distinct/Union without order by 1325
 QGWO 3 Group by/Distinct/Union without order by 1382
 QGWO 3 Group by/Distinct/Union without order by 1419
 QISO 3 Set isolation level 83
 QIWC 3 Insert with not all columns specified missing 1 columns out of 4 1246
 QJWT 3 Join or Sarg Without Index on temp table 582
 QJWT 3 Join or Sarg Without Index on temp table 650
 QJWT 3 Join or Sarg Without Index on temp table 664
 QJWT 3 Join or Sarg Without Index on temp table 813
 QJWT 3 Join or Sarg Without Index on temp table 816
 QJWT 3 Join or Sarg Without Index on temp table 823
 QJWT 3 Join or Sarg Without Index on temp table 934
 QJWT 3 Join or Sarg Without Index on temp table 937
 QJWT 3 Join or Sarg Without Index on temp table 943
 QJWT 3 Join or Sarg Without Index on temp table 1062
 QJWT 3 Join or Sarg Without Index on temp table 1065
 QJWT 3 Join or Sarg Without Index on temp table 1317
 QJWT 3 Join or Sarg Without Index on temp table 1368
 QJWT 3 Join or Sarg Without Index on temp table 1401
 QJWT 3 Join or Sarg Without Index on temp table 1462
 QNAJ 3 Not using ANSI Inner Join 483
 QNAJ 3 Not using ANSI Inner Join 495
 QNAJ 3 Not using ANSI Inner Join 516
 QNAJ 3 Not using ANSI Inner Join 573
 QNAJ 3 Not using ANSI Inner Join 646
 QNAJ 3 Not using ANSI Inner Join 660
 QNAJ 3 Not using ANSI Inner Join 673
 QNAJ 3 Not using ANSI Inner Join 810
 QNAJ 3 Not using ANSI Inner Join 931
 QNAJ 3 Not using ANSI Inner Join 1060
 QNAJ 3 Not using ANSI Inner Join 1193
 QNAJ 3 Not using ANSI Inner Join 1202
 QNAJ 3 Not using ANSI Inner Join 1218
 QNAJ 3 Not using ANSI Inner Join 1311
 QNAJ 3 Not using ANSI Inner Join 1362
 QNAJ 3 Not using ANSI Inner Join 1396
 QNAJ 3 Not using ANSI Inner Join 1456
 QNUA 3 Should use Alias: Column object_info1 should use alias a 572
 QNUA 3 Should use Alias: Table #rolenames 650
 QNUA 3 Should use Alias: Table #groupnames 664
 QNUA 3 Should use Alias: Column type should use alias a 1313
 QNUA 3 Should use Alias: Column type should use alias a 1364
 QNUA 3 Should use Alias: Column type should use alias a 1398
 QNUA 3 Should use Alias: Column type should use alias a 1458
 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}
359
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype, predid)
Intersection: {id}
651
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype, predid)
Intersection: {id}
665
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype, predid)
Intersection: {uid, id}
677
 QSWV 3 Sarg with variable @lowercase_permission_name, Candidate Index: spt_values.spt_valuesclust clustered(number, type) U 323
 QSWV 3 Sarg with variable @lowercase_permission_name, Candidate Index: spt_values.spt_valuesclust clustered(number, type) U 327
 QSWV 3 Sarg with variable @maxsysrolenum, Candidate Index: syssrvroles.csyssrvroles unique clustered(srid) F 500
 QSWV 3 Sarg with variable @user_suid, Candidate Index: sysusers.csysusers clustered(suid) F 615
 QTJ1 3 Table only appears in inner join clause 574
 QTLO 3 Top-Level OR 736
 QTLO 3 Top-Level OR 819
 QUNI 3 Check Use of 'union' vs 'union all' 1217
 QUNI 3 Check Use of 'union' vs 'union all' 1270
 VNRD 3 Variable is not read @name 260
 VUNU 3 Variable is not used @objid 43
 CUPD 2 Updatable Cursor Marker (updatable by default) 742
 CUPD 2 Updatable Cursor Marker (updatable by default) 1010
 MDYS 2 Dynamic SQL Marker 1144
 MDYS 2 Dynamic SQL Marker 1164
 MSUB 2 Subquery Marker 194
 MSUB 2 Subquery Marker 216
 MSUB 2 Subquery Marker 322
 MSUB 2 Subquery Marker 348
 MSUB 2 Subquery Marker 756
 MSUB 2 Subquery Marker 764
 MSUB 2 Subquery Marker 768
 MSUB 2 Subquery Marker 786
 MSUB 2 Subquery Marker 792
 MSUB 2 Subquery Marker 817
 MSUB 2 Subquery Marker 824
 MSUB 2 Subquery Marker 838
 MSUB 2 Subquery Marker 846
 MSUB 2 Subquery Marker 868
 MSUB 2 Subquery Marker 878
 MSUB 2 Subquery Marker 882
 MSUB 2 Subquery Marker 903
 MSUB 2 Subquery Marker 910
 MSUB 2 Subquery Marker 938
 MSUB 2 Subquery Marker 944
 MSUB 2 Subquery Marker 959
 MSUB 2 Subquery Marker 967
 MSUB 2 Subquery Marker 1022
 MSUB 2 Subquery Marker 1025
 MSUB 2 Subquery Marker 1045
 MSUB 2 Subquery Marker 1065
 MSUB 2 Subquery Marker 1078
 MSUC 2 Correlated Subquery Marker 1290
 MSUC 2 Correlated Subquery Marker 1340
 MSUC 2 Correlated Subquery Marker 1434
 MTR1 2 Metrics: Comments Ratio Comments: 39% 24
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 161 = 172dec - 13exi + 2 24
 MTR3 2 Metrics: Query Complexity Complexity: 693 24
 PRED_QUERY_COLLECTION 2 {l=master..sysloginroles, s=master..syssrvroles} 0 483
 PRED_QUERY_COLLECTION 2 {l=master..sysloginroles, s=master..syssrvroles} 0 495
 PRED_QUERY_COLLECTION 2 {l=master..sysloginroles, s=master..syssrvroles} 0 516

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#intmdt_role_set (1) 
read_writes table tempdb..#group_permissions (1) 
read_writes table tempdb..#sysprotects2 (1) 
reads table sybsystemprocs..sysobjects  
read_writes table tempdb..#sysprotects1 (1) 
reads table sybsystemprocs..sysprotects  
read_writes table tempdb..#intmdt2_role_set (1) 
reads table sybsystemprocs..sysusers  
read_writes table tempdb..#temp_role_set (1) 
reads table master..sysloginroles (1)  
reads table master..syslogins (1)  
read_writes table tempdb..#temp_obj (1) 
reads table master..spt_values (1)  
read_writes table tempdb..#groupnames (1) 
read_writes table tempdb..#rolenames (1) 
writes table tempdb..#sphelprotect1rs (1) 
reads table master..sysdatabases (1)  
read_writes table tempdb..#cum_role_set (1) 
read_writes table tempdb..#role_permissions (1) 
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
reads table master..syssrvroles (1)  
read_writes table tempdb..#temp (1) 
reads table sybsystemprocs..syscolumns  
writes table tempdb..#sphelprotect2rs (1) 
read_writes table tempdb..#temp1 (1) 
read_writes table tempdb..#user_permissions (1) 
read_writes table tempdb..#final_permissions (1) 
read_writes table tempdb..#temp_obj1 (1) 
calls proc sybsystemprocs..sp_autoformat  
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_005 
   read_writes table tempdb..#colinfo_af (1) 
   writes table sybsystemprocs..sp_autoformat_rset_003 
   reads table master..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   reads table tempdb..syscolumns (1)  
   reads table master..syscolumns (1)  
reads table master..sysattributes (1)