DatabaseProcApplicationCreatedLinks
sybsystemprocssp_displayroles  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     /*	5.0	1.0	06/10/96	sproc/src/displayroles */
4     
5     /*
6     ** Messages for "sp_displayroles"	18342
7     ** 18342, "Invalid name '%1!'. This role or user or login profile does not
8     **	  exist."
9     ** 18343, "Invalid mode '%1!'. Mode should either be 'expand_up' or 
10    **	  'expand_down'."
11    ** 18344, "The user name '%1!' should correspond to the current user or you 
12    **	  must possess System Security Officer (SSO) authorization to execute 
13    **	  '%2!' for another user."
14    ** 17020, "The specified login profile '%1!' must be associated with the current
15    **	  user or you must possess System Security Officer (SSO) authorization."
16    ** 18674, "Cannot display role information for a login or login profile name."
17    ** 18675, "Password expiration interval: %1!"
18    ** 18676, "Password Expired: %1!"
19    ** 18677, "Minimum Password Length: %1!"
20    ** 18678, "Maximum failed logins: %1!"
21    ** 18679, "Current failed login attempts: %1!"
22    ** 19872, "Role locked by '%1!' by manually executing alter role '%2!' lock."
23    ** 19873, "Role locked by ASE due to failed role activation attempts
24    **         reaching 'max failed_logins' limit."
25    ** 19645, "Password column corrupted"
26    */
27    
28    create or replace procedure sp_displayroles
29        @name varchar(30) = NULL, /* role name or login name
30        ** or login profile name
31        */
32        @mode varchar(30) = NULL /* "expand_up" or "expand_down" */
33    
34    as
35    
36        declare @grantee varchar(30) /* grantee name */
37        declare @loginame varchar(30) /* login name */
38        declare @hier_level int
39        declare @udr_class int /* UDR_CLASS value in sysattributes */
40        declare @udr_role_hier int /* value of attribute in sysattributes
41        ** for rows corresponding to role 
42        ** hierarchy
43        */
44        declare @msg varchar(1024) /* message holder */
45        declare @pwdate datetime /* last passwd set date */
46        declare @status smallint /* value of status */
47        declare @locked varchar(3) /* is the role locked? */
48        declare @lockdate datetime /* value of lockdate */
49        declare @locksuid int /* value of locksuid */
50        declare @lockingloginname varchar(30) /* Login name of locksuid */
51        declare @lockreason int /* value of lockreason */
52        declare @reason varchar(30) /* reason of locking role */
53        declare @passwdexp int /* value of passwd expiration */
54        declare @role_exp int /* role is expired */
55        declare @minpwdlen int /* value of minimum passwd 
56        ** length
57        */
58        declare @pwdexpired varchar(3) /* has the login's password 
59        ** expired?
60        */
61        declare @maxfailedlogins int /* value of max failed logins */
62        declare @currentfails int /* number of current failed 
63        ** logins
64        */
65        declare @srid int /* srid of the grantee */
66        declare @passeclass int /* class number of PASSWD_SECURITY
67        ** class in sysattributes
68        */
69        declare @local_msgbuf char(30) /* message holder */
70        declare @suid int /* suid of specified login or
71        ** or login profile
72        */
73        declare @user_lpid int /* associated login profile id of user
74        ** executing this procedure
75        */
76        declare @lprofile int /* status bit value for login profile */
77        declare @lprofile_def int /* status bit value for default
78        ** login profile
79        */
80        declare @passwordprot tinyint /* Role password protected? */
81        declare @vers tinyint /* Contains byte 2 of password column */
82        declare @encralgo varchar(30) /* Contains type of encryption algorithm
83        ** information
84        */
85        declare @def_lpid int /* default login profile id */
86        declare @profilename varchar(30) /* login profile name */
87        declare @lp_class int /* login profile class */
88        declare @default_lp_attr int /* sysattributes.attribute for
89        ** 'default login profile'
90        */
91        declare @lp_has_role int /* login profile has granted role(s) */
92        declare @dummy int /* Temporary holder */
93        declare @nullarg char(1) /* passing NULL */
94        declare @gp_enabled int /* granular permissions enabled ? */
95        declare @status1 int /* return status */
96        declare @status2 int /* returns status */
97    
98        select @status1 = 1
99        select @status2 = 1
100       select @role_exp = 0
101   
102       /* 
103       ** A @@trancount of 0 indicates no current transaction.
104       */
105       if @@trancount = 0
106       begin
107           /* In chained mode, SQL Server implicitly executes a "begin 
108           ** transaction" before the following statements: delete, fetch,
109           ** insert, open, select, and update.
110           */
111           set chained off
112       end
113   
114       /* Exclusive lock on objects being changed, held until transaction ends. No
115       ** shared locks
116       */
117       set transaction isolation level 1
118   
119       if (@name = 'help')
120       begin
121           print "sp_displayroles Usage: sp_displayroles [grantee_name [, mode]]"
122           print "grantee_name := login_name | role_name | login_profile_name"
123           print "mode := 'expand_up' | 'expand_down' | 'display_info'"
124           return (0)
125       end
126   
127       select @udr_class = class
128       from master.dbo.sysattributes
129       where object_type = "UR"
130   
131       select @lp_class = class
132       from master.dbo.sysattributes
133       where object_type = "LR"
134   
135       select @udr_role_hier = 2
136       select @user_lpid = - 1 /* Login profile is not associated */
137       select @lprofile = 512 /* bit value 0x0200 */
138       select @lprofile_def = 1024 /* bit value 0x0400 */
139       select @passwordprot = 0 /* Role is not password protected. */
140       select @def_lpid = null
141       select @profilename = null
142       select @default_lp_attr = 4 /* sysattributes.attribute for
143       ** 'default login profile'
144       */
145       select @lp_has_role = 0
146   
147       /* Convert the first and last args to lower case with no leading/trailing
148       ** blanks.
149       */
150       if @mode is not NULL
151           select @mode = lower(ltrim(rtrim(@mode)))
152   
153       if @name is NULL
154       begin
155           if (@mode = "display_info")
156           begin
157               /*
158               ** 18674, "Cannot display role information
159               ** for a login or login profile name."
160               */
161               raiserror 18674
162               return (1)
163           end
164           else
165           begin
166               select @name = suser_name()
167               select @loginame = @name
168               select @grantee = @name
169               select @status = status, @suid = suid
170               from master.dbo.syslogins
171               where name = @name
172           end
173       end
174       else
175       begin
176           /* check if name is a valid loginname or login profile name */
177           if exists (select 1 from master.dbo.syslogins
178                   where name = @name)
179           begin
180               if (@mode = "display_info")
181               begin
182                   /*
183                   ** 18674, "Cannot display role information
184                   ** for a login or login profile name."
185                   */
186                   raiserror 18674
187                   return (1)
188               end
189               else
190               begin
191                   select @grantee = @name
192                   select @loginame = @name
193                   select @status = status, @suid = suid
194                   from master.dbo.syslogins
195                   where name = @name
196               end
197   
198           end
199           else
200           begin
201               if exists (select * from master.dbo.syssrvroles
202                       where name = @name)
203               begin
204                   select @grantee = @name
205                   select @loginame = NULL
206               end
207               else
208               begin
209                   /* 18342, "Invalid name '%1!'. This role or user or
210                   ** login profile does not exist."
211                   */
212                   raiserror 18342, @name
213                   return (1)
214               end
215           end
216       end
217   
218       if @mode is NULL
219           select @mode = "direct"
220   
221       /* verify mode is set to "expand_up" or "expand_down" or "display_info" */
222       else if (@mode != "expand_down" and @mode != "expand_up" and
223               @mode != "display_info")
224       begin
225           /* 18343, "Invalid mode '%1!'. Mode should either be 'expand_up'
226           ** or 'expand_down' or 'display_info'."
227           */
228           raiserror 18343, @mode
229           return (1)
230       end
231   
232       /* Display role related info, that is requested & exit */
233       if (@mode = "display_info")
234       begin
235           /* NOTE: For all the password control related 
236           ** information printed below, a check is first 
237           ** made if the specified role has a value 
238           ** for the attribute in sysattribute. If there
239           ** is no configured value, then the systemwide
240           ** default is picked up from sysconfigures
241           */
242           select @srid = srid from master.dbo.syssrvroles
243           where name = @grantee
244   
245           /* Print the role name */
246           print "Role name : %1!", @grantee
247   
248           /* Print the role locked information */
249           select @status = status,
250               @lockdate = lockdate,
251               @locksuid = locksuid,
252               @lockreason = lockreason
253           from master.dbo.syssrvroles
254           where name = @grantee
255           if (@status = 2)
256           begin
257               /* Role is locked */
258               select @locked = "YES"
259               print "Locked : %1!", @locked
260               print "    Date when locked : %1!", @lockdate
261   
262               if (@lockreason = 1)
263               begin
264                   /*
265                   ** 19872, "Role locked by '%1!' by manually executing
266                   **	   alter role '%2!' lock."
267                   */
268                   exec sp_getmessage 19872, @msg output
269                   select @msg = "    Reason : " + @msg
270                   select @lockingloginname = suser_name(@locksuid)
271                   print @msg, @lockingloginname, @grantee
272               end
273               else if (@lockreason = 2)
274               begin
275                   /*
276                   ** 19873, "Role locked by ASE due to failed role
277                   ** 	   activation attempts reaching
278                   **	   'max failed_logins' limit."
279                   */
280                   exec sp_getmessage 19873, @msg output
281                   select @msg = "    Reason : " + @msg
282                   print @msg
283                   select @lockingloginname = suser_name(@locksuid)
284               end
285   
286               print "    Locking suid : %1!", @lockingloginname
287           end
288           else
289           begin
290               /* Role is not locked */
291               select @locked = "NO"
292               print "Locked : %1!", @locked
293           end
294   
295           /* Date of last password change */
296           select @pwdate = pwdate from master.dbo.syssrvroles
297           where name = @grantee
298           print "Date of Last Password change : %1!", @pwdate
299   
300           /* set the value of PASSWD_SECURITY class 
301           */
302           select @passeclass = class from master.dbo.sysattributes where
303               object_type = "PS"
304   
305           /* Password expiration interval */
306           select @passwdexp = int_value
307           from master.dbo.sysattributes
308           where class = @passeclass AND
309               attribute = 0 AND
310               object = @srid AND object_cinfo = "role"
311   
312           if (@passwdexp is NULL)
313           begin
314               /*
315               ** Check if the global password expiration has been configured
316               ** using sp_passwordpolicy.
317               */
318               select @passwdexp = int_value
319               from master.dbo.sysattributes
320               where class = 27 AND /* password complexity checks class */
321                   attribute = 7 /* systemwide password expiration attribute */
322   
323               if (@passwdexp is NULL)
324               begin
325                   select @passwdexp = value
326                   from master.dbo.sysconfigures
327                   where name = "systemwide password expiration"
328               end
329           end
330   
331           /*
332           ** 18675, "Password expiration interval: %1!"
333           */
334           exec sp_getmessage 18675, @msg output
335           select @local_msgbuf = convert(char(10), @passwdexp)
336           print @msg, @local_msgbuf
337   
338   
339           /* Has the role's password expired?? */
340           select @role_exp = (status & 4)
341           from master.dbo.syssrvroles
342           where srid = @srid
343   
344           if (@role_exp = 4)
345           begin
346               select @pwdexpired = "YES"
347           end
348           else if (@passwdexp = 0)
349           begin
350               select @pwdexpired = "NO"
351           end
352           else
353           if (datediff(dd, @pwdate, getdate()) > @passwdexp)
354               select @pwdexpired = "YES"
355           else
356               select @pwdexpired = "NO"
357   
358           /*
359           ** 18676, "Password Expired: %1!"
360           */
361           exec sp_getmessage 18676, @msg output
362           select @local_msgbuf = upper(@pwdexpired)
363           print @msg, @local_msgbuf
364   
365           /* Minimum password length */
366           select @minpwdlen = int_value
367           from master.dbo.sysattributes
368           where class = @passeclass AND
369               attribute = 1 AND
370               object = @srid AND object_cinfo = "role"
371   
372           if (@minpwdlen is NULL)
373           begin
374               select @minpwdlen = value
375               from master.dbo.sysconfigures
376               where name = "minimum password length"
377           end
378   
379           /*
380           ** 18677, "Minimum Password Length: %1!"
381           */
382           exec sp_getmessage 18677, @msg output
383           select @local_msgbuf = convert(char(10), @minpwdlen)
384           print @msg, @local_msgbuf
385   
386   
387           /* Maximum failed logins */
388           select @maxfailedlogins = int_value
389           from master.dbo.sysattributes
390           where class = @passeclass AND
391               attribute = 2 AND object = @srid
392               AND object_cinfo = "role"
393   
394           if (@maxfailedlogins is NULL)
395           begin
396               select @maxfailedlogins = value
397               from master.dbo.sysconfigures
398               where name = "maximum failed logins"
399           end
400   
401           /*
402           ** 18678, "Maximum failed logins: %1!"
403           */
404           exec sp_getmessage 18678, @msg output
405           select @local_msgbuf = convert(char(10), @maxfailedlogins)
406           print @msg, @local_msgbuf
407   
408           /* Current failed logins count */
409           select @currentfails = logincount
410           from master.dbo.syssrvroles
411           where srid = @srid
412   
413           /*
414           ** 18679, "Current failed login attempts: %1!"
415           */
416           exec sp_getmessage 18679, @msg output
417           select @local_msgbuf = convert(char(10), @currentfails)
418           print @msg, @local_msgbuf
419   
420           /* 
421           ** Select password encryption algorithm information,
422           ** if role is password protected.
423           */
424           select @passwordprot = 1, @vers = substring(password, 2, 1)
425           from master.dbo.syssrvroles
426           where name = @name and password != NULL
427           if (@passwordprot = 1)
428           begin
429               /* Values 1 to 5 and 7 are valid for @vers field */
430               if @vers <= 5 and @vers >= 1
431               begin
432                   select @encralgo = "SYB-PROP"
433               end
434               else if @vers = 7
435               begin
436                   select @encralgo = "SHA-256"
437               end
438               else
439               begin
440                   /*      
441                   ** 19645, "Password column corrupted"
442                   */
443                   exec sp_getmessage 19645, @msg output
444                   select @encralgo = @msg
445               end
446               select @msg = "Password encryption version: %1!"
447               print @msg, @encralgo
448           end
449   
450           return (0)
451       end
452       else
453       if (@mode = "expand_up")
454           select @hier_level = 0
455       else
456           select @hier_level = 1
457   
458       /* create a temporary table to hold the intermediate result of expansion */
459   
460       create table #intermediate_roleset
461       (rolename varchar(30) not null,
462           parentrolename varchar(30) null,
463           role_level smallint null,
464           grantee varchar(30) null)
465   
466       if (@grantee = @loginame)
467       begin
468           /* Check that user has appropriate permissions */
469   
470           /* grantee is a login profile */
471           if ((@status & @lprofile) = @lprofile)
472           begin
473               /* Get the associated login profile id for this user */
474               select @user_lpid = lpid from master.dbo.syslogins
475               where name = suser_name()
476   
477               /*
478               ** No permission error if any of the following:
479               ** 1) specified login profile is associated to this user
480               ** 2) specified login profile is default login profile and
481               **    some login profile is applicable to this user (i.e.
482               **    associated login profile id is not set to -1 (ignore))
483               ** 3) SSO role is enabled for this user
484               ** 4) MANAGE_ANY_LOGIN_PROFILE previlege is granted for this user
485               ** RESOLVE:
486               **	MANAGE_ANY_LOGIN_PROFILE previlege is yet not defined
487               **	in asebharani. This is part of Granular Permissions
488               **	project.
489               */
490               if (not ((@user_lpid = @suid)
491                           or ((@user_lpid != - 1)
492                               and ((@status & @lprofile_def) = @lprofile_def))
493                           or (charindex("sso_role", show_role()) != 0)))
494               begin
495                   /* 17020, "The specified login profile '%1!' must be
496                   ** associated with the current user or you must possess
497                   ** System Security Officer (SSO) authorization."
498                   */
499                   raiserror 17020, @name
500                   return (1)
501               end
502           end
503           /* grantee is a login */
504           else if (@name != suser_name())
505           begin
506               /*
507               **  If granular permissions is not enabled, 
508               **  Accounts with SSO role can execute it.
509               **  If granular permissions is enabled then users with 
510               ** 'manage roles' or 'manage server' permission can execute it.
511               */
512               select @nullarg = NULL
513               execute @status1 = sp_aux_checkroleperm "sso_role",
514                   "manage roles", @nullarg, @gp_enabled output
515   
516               if (@gp_enabled = 0)
517               begin
518                   if (@status1 != 0)
519                   begin
520                       /* 18344, "The user name '%1!' should correspond
521                       **  to the current user or you must possess 
522                       ** System Security Officer (SSO) authorization 
523                       ** to execute '%2!' for another user." 
524                       */
525                       raiserror 18344, @name, "sp_displayroles"
526                       return (1)
527                   end
528                   if (@status1 = 0)
529                       select @dummy = proc_role("sso_role")
530               end
531               else
532               begin
533                   if (@status1 != 0)
534                   begin
535                       select @nullarg = NULL
536                       execute @status2 = sp_aux_checkroleperm
537                           @nullarg, "manage server",
538                           @nullarg, @gp_enabled output
539                       if (@status2 != 0)
540                       begin
541                           select @dummy =
542                               proc_auditperm("manage roles",
543                                   @status1)
544                           select @dummy =
545                               proc_auditperm("manage server",
546                                   @status2)
547                           return 1
548                       end
549                   end
550                   if (@status1 = 0)
551                       select @dummy = proc_auditperm("manage roles ",
552                               @status1)
553                   if (@status2 = 0)
554                       select @dummy = proc_auditperm("manage server",
555                               @status2)
556   
557               end
558           end
559   
560           /* select all names from syssrvroles for this user/login profile and
561           ** insert into #intermediate_roleset
562           */
563           insert into #intermediate_roleset(rolename, role_level, grantee)
564           select r.name, @hier_level, @grantee
565           from master.dbo.sysloginroles l,
566               master.dbo.syssrvroles r
567           where l.srid = r.srid
568               and l.suid = @suid
569   
570           /* for loginname */
571           if (((@status & @lprofile) != @lprofile) and @mode != "expand_up")
572           begin
573               /* Get the associated login profile id for this user */
574               select @user_lpid = lpid from master.dbo.syslogins
575               where name = @grantee
576               if (@user_lpid is NULL)
577               begin
578                   /* default login profile is applicable */
579                   select @def_lpid = object
580                   from master.dbo.sysattributes
581                   where class = @lp_class and attribute = @default_lp_attr
582   
583                   if (@def_lpid is not null)
584                   begin
585                       select @profilename = name
586                       from master.dbo.syslogins
587                       where suid = @def_lpid
588   
589                       insert into #intermediate_roleset(rolename,
590                           role_level, grantee)
591                       select r.name, @hier_level,
592                           @profilename
593                       from master.dbo.sysloginroles l,
594                           master.dbo.syssrvroles r
595                       where l.srid = r.srid
596                           and l.suid = @def_lpid
597                       select @lp_has_role = @@rowcount
598                   end
599               end
600               else if (@user_lpid != - 1)
601               begin
602                   /* login profile is associated with this login */
603                   select @profilename = name from master.dbo.syslogins
604                   where suid = @user_lpid
605                   insert into #intermediate_roleset(rolename,
606                       role_level, grantee)
607                   select r.name, @hier_level, @profilename
608                   from master.dbo.sysloginroles l,
609                       master.dbo.syssrvroles r
610                   where l.srid = r.srid
611                       and l.suid = @user_lpid
612                   select @lp_has_role = @@rowcount
613               end
614           /* else login profile is ignored for this login */
615           end
616       end
617   
618       else
619       begin
620           /* It is for a role. */
621   
622           if (@mode != "expand_up")
623           begin
624               /* select all rolenames from sysattributes
625               ** which are directly contained by this role and insert into 
626               ** #intermediate_roleset 
627               */
628   
629               insert into #intermediate_roleset(rolename, parentrolename,
630                   role_level)
631               select role_name(object_info1), @grantee, @hier_level
632               from master.dbo.sysattributes
633               where object = role_id(@grantee)
634                   and class = @udr_class
635                   and attribute = @udr_role_hier
636           end
637   
638           else
639           begin
640               /* select all rolenames from sysattributes
641               ** which are parents of this role and insert into
642               ** #intermediate_roleset
643               */
644               insert into #intermediate_roleset(rolename, parentrolename,
645                   role_level)
646               select @grantee, role_name(object), @hier_level
647               from master.dbo.sysattributes
648               where object_info1 = role_id(@grantee)
649                   and class = @udr_class
650                   and attribute = @udr_role_hier
651   
652           end
653       end
654   
655       /* If mode is 'direct' then display only directly granted roles to 
656       ** role_name that was specified.
657       */
658       if (@mode = "direct")
659       begin
660           /*
661           ** Display the rows from #intermediate_roleset and return.
662           ** 'Grantee' column is only displayed when a login has associated
663           ** login profile or default login profile is applicable and
664           ** roles are granted to it.
665           */
666           if (@lp_has_role = 0)
667           begin
668               select "Role Name" = rolename
669               from #intermediate_roleset
670           end
671           else
672           begin
673               select "Role Name" = rolename,
674                   "Grantee" = grantee
675               from #intermediate_roleset
676           end
677   
678           return (0)
679       end
680   
681       /* create two more temp tables, #cumulative_role_set and #temp_role_set */
682   
683       create table #cumulative_role_set
684       (rolename varchar(30) not null,
685           parentrolename varchar(30) null,
686           role_level smallint null,
687           grantee varchar(30) null)
688   
689       create table #temp_role_set
690       (rolename varchar(30) not null,
691           parentrolename varchar(30) null,
692           role_level smallint null,
693           grantee varchar(30) null)
694   
695       /* 
696       ** For the while loop below,
697       **	the start condition is:
698       **		#cumulative_role_set contains nothing
699       **		#intermediate_roleset contains the base set of roles
700       **		#temp_roleset contains nothing
701       */
702   
703       while exists (select * from #intermediate_roleset)
704       begin
705           if (@mode = "expand_down")
706           begin
707               select @hier_level = @hier_level + 1
708   
709               /* insert into #temp_role_set(role_level, select role1, role2 
710               ** from sysattributes where row is of type 'role1 contains 
711               ** role2' and role1 is in #intermediate_roleset)
712               */
713   
714               insert into #temp_role_set(rolename, parentrolename, role_level)
715               select role_name(object_info1), rolename, @hier_level
716               from master.dbo.sysattributes, #intermediate_roleset
717               where object = role_id(rolename)
718                   and class = @udr_class
719                   and attribute = @udr_role_hier
720           end
721   
722           else
723           begin
724               select @hier_level = @hier_level - 1
725   
726               /* insert into #temp_role_set(role_level, select role1, role2
727               ** from sysattributes where row is of type 'role1 is contained
728               ** by role2' and role1 is in #intermediate_roleset)
729               */
730   
731               insert into #temp_role_set(rolename, parentrolename, role_level)
732               select parentrolename, role_name(object), @hier_level
733               from master.dbo.sysattributes, #intermediate_roleset
734               where object_info1 = role_id(parentrolename)
735                   and class = @udr_class
736                   and attribute = @udr_role_hier
737           end /* Adaptive Server has expanded all '*' elements in the following statement */
738   
739   
740           /* copy all rows from #intermediate_roleset into #cumulative_role_set */
741           insert into #cumulative_role_set
742           select #intermediate_roleset.rolename, #intermediate_roleset.parentrolename, #intermediate_roleset.role_level, #intermediate_roleset.grantee
743           from #intermediate_roleset
744           where rolename not in (select rolename from #cumulative_role_set)
745               or parentrolename not in (select parentrolename
746                   from #cumulative_role_set
747                   where parentrolename is not null)
748   
749           /* delete all rows from #intermediate_roleset */
750           delete from #intermediate_roleset
751   
752           /* copy all rows from #temp_role_set into #intermediate_roleset */
753           /* Adaptive Server has expanded all '*' elements in the following statement */ insert into #intermediate_roleset
754           select #temp_role_set.rolename, #temp_role_set.parentrolename, #temp_role_set.role_level, #temp_role_set.grantee
755           from #temp_role_set
756   
757           /* delete all rows from #temp_roleset */
758           delete from #temp_role_set
759       end
760   
761       /* select all rows from #cumulative_role_set and display it */
762       if (@lp_has_role = 0)
763       begin
764           select distinct "Role Name" = rolename,
765               "Parent Role Name" = parentrolename,
766               "Level" = role_level
767           from #cumulative_role_set
768           order by role_level
769       end
770       else
771       begin
772           select distinct "Role Name" = rolename,
773               "Parent Role Name" = parentrolename,
774               "Level" = role_level,
775               "Grantee" = grantee
776           from #cumulative_role_set
777           order by role_level
778       end
779   
780       return (0)
781   


exec sp_procxmode 'sp_displayroles', 'AnyMode'
go

Grant Execute on sp_displayroles to public
go
RESULT SETS
sp_displayroles_rset_004
sp_displayroles_rset_003
sp_displayroles_rset_002
sp_displayroles_rset_001

DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MTYP 4 Assignment type mismatch @encralgo: varchar(30) = varchar(1024) 444
 MTYP 4 Assignment type mismatch role_level: smallint = int 564
 MTYP 4 Assignment type mismatch role_level: smallint = int 591
 MTYP 4 Assignment type mismatch role_level: smallint = int 607
 MTYP 4 Assignment type mismatch role_level: smallint = int 631
 MTYP 4 Assignment type mismatch role_level: smallint = int 646
 MTYP 4 Assignment type mismatch role_level: smallint = int 715
 MTYP 4 Assignment type mismatch role_level: smallint = int 732
 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}
129
 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}
133
 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}
303
 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]
717
 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_info1}
Uncovered: [object_type, object, object_info2, object_info3, object_cinfo]
734
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 308
 QTYP 4 Comparison type mismatch smallint = int 308
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 309
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 320
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 321
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 368
 QTYP 4 Comparison type mismatch smallint = int 368
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 369
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 390
 QTYP 4 Comparison type mismatch smallint = int 390
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 391
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 581
 QTYP 4 Comparison type mismatch smallint = int 581
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 634
 QTYP 4 Comparison type mismatch smallint = int 634
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 635
 QTYP 4 Comparison type mismatch smallint = int 635
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 649
 QTYP 4 Comparison type mismatch smallint = int 649
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 650
 QTYP 4 Comparison type mismatch smallint = int 650
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 718
 QTYP 4 Comparison type mismatch smallint = int 718
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 719
 QTYP 4 Comparison type mismatch smallint = int 719
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 735
 QTYP 4 Comparison type mismatch smallint = int 735
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 736
 QTYP 4 Comparison type mismatch smallint = int 736
 TNOU 4 Table with no unique index master..sysloginroles master..sysloginroles
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysconfigures  
 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_displayroles  
 MNAC 3 Not using ANSI 'is null' 426
 MNER 3 No Error Check should check return value of exec 268
 MNER 3 No Error Check should check return value of exec 280
 MNER 3 No Error Check should check return value of exec 334
 MNER 3 No Error Check should check return value of exec 361
 MNER 3 No Error Check should check return value of exec 382
 MNER 3 No Error Check should check return value of exec 404
 MNER 3 No Error Check should check return value of exec 416
 MNER 3 No Error Check should check return value of exec 443
 MNER 3 No Error Check should check @@error after insert 563
 MNER 3 No Error Check should check @@error after insert 589
 MNER 3 No Error Check should check @@error after insert 605
 MNER 3 No Error Check should check @@error after insert 629
 MNER 3 No Error Check should check @@error after insert 644
 MNER 3 No Error Check should check @@error after insert 714
 MNER 3 No Error Check should check @@error after insert 731
 MNER 3 No Error Check should check @@error after insert 741
 MNER 3 No Error Check should check @@error after delete 750
 MNER 3 No Error Check should check @@error after insert 753
 MNER 3 No Error Check should check @@error after delete 758
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 155
 MUCO 3 Useless Code Useless Brackets 162
 MUCO 3 Useless Code Useless Brackets 180
 MUCO 3 Useless Code Useless Brackets 187
 MUCO 3 Useless Code Useless Brackets 213
 MUCO 3 Useless Code Useless Brackets 222
 MUCO 3 Useless Code Useless Brackets 229
 MUCO 3 Useless Code Useless Brackets 233
 MUCO 3 Useless Code Useless Brackets 255
 MUCO 3 Useless Code Useless Brackets 262
 MUCO 3 Useless Code Useless Brackets 273
 MUCO 3 Useless Code Useless Brackets 312
 MUCO 3 Useless Code Useless Brackets 323
 MUCO 3 Useless Code Useless Brackets 344
 MUCO 3 Useless Code Useless Brackets 348
 MUCO 3 Useless Code Useless Brackets 353
 MUCO 3 Useless Code Useless Brackets 372
 MUCO 3 Useless Code Useless Brackets 394
 MUCO 3 Useless Code Useless Brackets 427
 MUCO 3 Useless Code Useless Brackets 450
 MUCO 3 Useless Code Useless Brackets 453
 MUCO 3 Useless Code Useless Brackets 466
 MUCO 3 Useless Code Useless Brackets 471
 MUCO 3 Useless Code Useless Brackets 490
 MUCO 3 Useless Code Useless Brackets 500
 MUCO 3 Useless Code Useless Brackets 504
 MUCO 3 Useless Code Useless Brackets 516
 MUCO 3 Useless Code Useless Brackets 518
 MUCO 3 Useless Code Useless Brackets 526
 MUCO 3 Useless Code Useless Brackets 528
 MUCO 3 Useless Code Useless Brackets 533
 MUCO 3 Useless Code Useless Brackets 539
 MUCO 3 Useless Code Useless Brackets 550
 MUCO 3 Useless Code Useless Brackets 553
 MUCO 3 Useless Code Useless Brackets 571
 MUCO 3 Useless Code Useless Brackets 576
 MUCO 3 Useless Code Useless Brackets 583
 MUCO 3 Useless Code Useless Brackets 600
 MUCO 3 Useless Code Useless Brackets 622
 MUCO 3 Useless Code Useless Brackets 658
 MUCO 3 Useless Code Useless Brackets 666
 MUCO 3 Useless Code Useless Brackets 678
 MUCO 3 Useless Code Useless Brackets 705
 MUCO 3 Useless Code Useless Brackets 762
 MUCO 3 Useless Code Useless Brackets 780
 QAFM 3 Var Assignment from potentially many rows 127
 QAFM 3 Var Assignment from potentially many rows 131
 QAFM 3 Var Assignment from potentially many rows 242
 QAFM 3 Var Assignment from potentially many rows 249
 QAFM 3 Var Assignment from potentially many rows 296
 QAFM 3 Var Assignment from potentially many rows 302
 QAFM 3 Var Assignment from potentially many rows 306
 QAFM 3 Var Assignment from potentially many rows 318
 QAFM 3 Var Assignment from potentially many rows 325
 QAFM 3 Var Assignment from potentially many rows 366
 QAFM 3 Var Assignment from potentially many rows 374
 QAFM 3 Var Assignment from potentially many rows 388
 QAFM 3 Var Assignment from potentially many rows 396
 QAFM 3 Var Assignment from potentially many rows 424
 QAFM 3 Var Assignment from potentially many rows 579
 QCRS 3 Conditional Result Set 668
 QCRS 3 Conditional Result Set 673
 QCRS 3 Conditional Result Set 764
 QCRS 3 Conditional Result Set 772
 QISO 3 Set isolation level 117
 QIWC 3 Insert with not all columns specified missing 1 columns out of 4 563
 QIWC 3 Insert with not all columns specified missing 1 columns out of 4 589
 QIWC 3 Insert with not all columns specified missing 1 columns out of 4 605
 QIWC 3 Insert with not all columns specified missing 1 columns out of 4 629
 QIWC 3 Insert with not all columns specified missing 1 columns out of 4 644
 QIWC 3 Insert with not all columns specified missing 1 columns out of 4 714
 QIWC 3 Insert with not all columns specified missing 1 columns out of 4 731
 QJWT 3 Join or Sarg Without Index on temp table 744
 QJWT 3 Join or Sarg Without Index on temp table 745
 QNAJ 3 Not using ANSI Inner Join 565
 QNAJ 3 Not using ANSI Inner Join 593
 QNAJ 3 Not using ANSI Inner Join 608
 QNAJ 3 Not using ANSI Inner Join 716
 QNAJ 3 Not using ANSI Inner Join 733
 QNUA 3 Should use Alias: Column object_info1 should use alias sysattributes 715
 QNUA 3 Should use Alias: Column rolename should use alias #intermediate_roleset 715
 QNUA 3 Should use Alias: Table #intermediate_roleset 716
 QNUA 3 Should use Alias: Table master..sysattributes 716
 QNUA 3 Should use Alias: Column object should use alias sysattributes 717
 QNUA 3 Should use Alias: Column rolename should use alias #intermediate_roleset 717
 QNUA 3 Should use Alias: Column class should use alias sysattributes 718
 QNUA 3 Should use Alias: Column attribute should use alias sysattributes 719
 QNUA 3 Should use Alias: Column object should use alias sysattributes 732
 QNUA 3 Should use Alias: Column parentrolename should use alias #intermediate_roleset 732
 QNUA 3 Should use Alias: Table #intermediate_roleset 733
 QNUA 3 Should use Alias: Table master..sysattributes 733
 QNUA 3 Should use Alias: Column object_info1 should use alias sysattributes 734
 QNUA 3 Should use Alias: Column parentrolename should use alias #intermediate_roleset 734
 QNUA 3 Should use Alias: Column class should use alias sysattributes 735
 QNUA 3 Should use Alias: Column attribute should use alias sysattributes 736
 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: {object, object_cinfo, attribute, class}
308
 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}
320
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
327
 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: {object, object_cinfo, attribute, class}
368
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
376
 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: {object, object_cinfo, attribute, class}
390
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
398
 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}
581
 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: {object, class, attribute}
633
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_info1, attribute}
648
 QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) F 568
 QSWV 3 Sarg with variable @def_lpid, Candidate Index: sysloginroles.csysloginroles clustered(suid) F 596
 QSWV 3 Sarg with variable @user_lpid, Candidate Index: sysloginroles.csysloginroles clustered(suid) F 611
 QTLO 3 Top-Level OR 744
 VNRD 3 Variable is not read @gp_enabled 538
 VNRD 3 Variable is not read @dummy 554
 VUNU 3 Variable is not used @reason 52
 MRST 2 Result Set Marker 668
 MRST 2 Result Set Marker 673
 MRST 2 Result Set Marker 764
 MRST 2 Result Set Marker 772
 MSUB 2 Subquery Marker 177
 MSUB 2 Subquery Marker 201
 MSUB 2 Subquery Marker 745
 MTR1 2 Metrics: Comments Ratio Comments: 38% 28
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 57 = 66dec - 11exi + 2 28
 MTR3 2 Metrics: Query Complexity Complexity: 398 28
 PRED_QUERY_COLLECTION 2 {l=master..sysloginroles, s=master..syssrvroles} 0 564
 PRED_QUERY_COLLECTION 2 {l=master..sysloginroles, s=master..syssrvroles} 0 591
 PRED_QUERY_COLLECTION 2 {l=master..sysloginroles, s=master..syssrvroles} 0 607

DATA PROPAGATION detailed
ColumnWritten To
@namesp_displayroles_rset_001.Role Name sp_displayroles_rset_002.Role Name °.Grantee sp_displayroles_rset_003.Role Name °.Parent Role Name sp_displayroles_rset_004.Role Name

DEPENDENCIES
PROCS AND TABLES USED
reads table master..syssrvroles (1)  
writes table sybsystemprocs..sp_displayroles_rset_002 
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
read_writes table tempdb..#intermediate_roleset (1) 
writes table sybsystemprocs..sp_displayroles_rset_003 
read_writes table tempdb..#cumulative_role_set (1) 
read_writes table tempdb..#temp_role_set (1) 
reads table master..sysloginroles (1)  
writes table sybsystemprocs..sp_displayroles_rset_001 
writes table sybsystemprocs..sp_displayroles_rset_004 
reads table master..sysattributes (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
reads table master..syslogins (1)  
reads table master..sysconfigures (1)