| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_activeroles | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 /* 5.0 1.0 01/28/97 sproc/src/activeroles */ 4 5 /* 6 ** Messages for "sp_activeroles" 18341 7 ** 18341, "An invalid argument was entered. Usage: sp_activeroles [expand_down]" 8 */ 9 10 create procedure sp_activeroles 11 @mode varchar(30) = NULL /* "expand_down" only */ 12 13 as 14 15 declare @hier_level int 16 , @udr_class int /* value of UDR_CLASS in sysattrib's */ 17 , @udr_role_hier int /* value of attribute in sysattributes 18 ** for rows corresponding to role 19 ** hierarchy 20 */ 21 , @msg varchar(255) /* message holder */ 22 , @rolelist varchar(255) /* list of roles from show_role() */ 23 , @maxsysrolenum int 24 , @lpid int 25 26 select @maxsysrolenum = 31 27 select @lpid = NULL 28 29 /* 30 ** A @@trancount of 0 indicates no current transaction. 31 */ 32 if @@trancount = 0 33 begin 34 /* In chained mode, SQL Server implicitly executes a "begin 35 ** transaction" before the following statements: delete, fetch, 36 ** insert, open, select, and update. 37 */ 38 set chained off 39 end 40 41 /* Exclusive lock on objects being changed, held until transaction ends. No 42 ** shared locks 43 */ 44 set transaction isolation level 1 45 46 /* Initialize variables */ 47 select @udr_class = class 48 from master.dbo.sysattributes 49 where object_type = "UR" 50 51 select @udr_role_hier = 2 52 53 /* Convert arg to lower case with no leading/trailing blanks. */ 54 if @mode is not NULL 55 begin 56 select @mode = lower(ltrim(rtrim(@mode))) 57 58 if (@mode != "expand_down") 59 begin 60 /* 18341, "An invalid argument was entered. 61 ** Usage: sp_activeroles [expand_down]." 62 */ 63 raiserror 18341 64 return (1) 65 end 66 end 67 68 select @hier_level = 1 69 70 /* 71 ** Get user's login profile id, if any; otherwise see if there's a default 72 */ 73 select @lpid = lpid from master.dbo.syslogins 74 where name = suser_name() 75 if (@lpid = NULL) 76 begin 77 select @lpid = object from master.dbo.sysattributes 78 where class = 39 and attribute = 4 and object_type = "LR" 79 end 80 /* 81 ** Create a temporary table to hold the intermediate result of expansion 82 */ 83 create table #intermediate_roleset 84 (rolename varchar(30) not null, 85 parentrolename varchar(30) null, 86 role_level smallint null) 87 88 /* create another temporary table to hold another result of expansion */ 89 90 create table #tmp_roleset 91 (rolename varchar(30) not null, 92 parentrolename varchar(30) null, 93 role_level smallint null) 94 95 /* 96 ** Select all rolenames from syssrvroles for this user and 97 ** his login profile, and insert the ones for which 98 ** charindex(r.name, show_role()) returns > 0 into 99 ** #intermediate_roleset. This gives the active system roles 100 ** without using a call to proc_role(). 101 */ 102 select @rolelist = show_role() 103 104 insert into #intermediate_roleset(rolename, role_level) 105 select distinct r.name, @hier_level 106 from master.dbo.sysloginroles l, 107 master.dbo.syssrvroles r 108 where l.srid = r.srid 109 and charindex(r.name, @rolelist) > 0 110 and (l.suid = suser_id() 111 or (@lpid != NULL and l.suid = @lpid)) 112 113 /* 114 ** Get the user-defined roles for this user and his login profile. 115 ** We will need to call proc_role() on these. 116 */ 117 insert into #tmp_roleset(rolename, role_level) 118 select distinct r.name, @hier_level 119 from master.dbo.sysloginroles l, 120 master.dbo.syssrvroles r 121 where l.srid = r.srid 122 and r.srid > @maxsysrolenum 123 and (l.suid = suser_id() 124 or (@lpid != NULL and l.suid = @lpid)) 125 126 /* Call proc_role() on the user-defined roles to see which 127 ** are active, and insert them into the intermediate table. 128 */ 129 insert into #intermediate_roleset(rolename, role_level) 130 select rolename, role_level from #tmp_roleset t 131 where proc_role(t.rolename) = 1 132 133 if @mode is NULL 134 begin 135 /* display the rows from #intermediate_roleset and return */ 136 137 select "Role Name" = rolename 138 from #intermediate_roleset 139 140 return (0) 141 end 142 143 /* create two more temp tables, #cumulative_role_set and #temp_role_set */ 144 create table #cumulative_role_set 145 (rolename varchar(30) not null, 146 parentrolename varchar(30) null, 147 role_level smallint null) 148 149 create table #temp_role_set 150 (rolename varchar(30) not null, 151 parentrolename varchar(30) null, 152 role_level smallint null) 153 154 /* 155 ** For the while loop below, 156 ** the start condition is: 157 ** #cumulative_role_set contains nothing 158 ** #intermediate_roleset contains the base set of roles 159 ** #temp_roleset contains nothing 160 */ 161 162 while exists (select * from #intermediate_roleset) 163 begin 164 select @hier_level = @hier_level + 1 165 166 /* insert into #temp_role_set(role_level, select role1, role2 167 ** from sysattributes where row is of type 'role1 contains 168 ** role2' and role1 is in #intermediate_roleset) 169 */ 170 171 insert into #temp_role_set(rolename, parentrolename, role_level) 172 select role_name(object_info1), rolename, @hier_level 173 from master.dbo.sysattributes, #intermediate_roleset 174 where object = role_id(rolename) 175 and class = @udr_class 176 and attribute = @udr_role_hier 177 178 /* copy all rows from #intermediate_roleset into #cumulative_role_set*/ 179 /* Adaptive Server has expanded all '*' elements in the following statement */ insert into #cumulative_role_set 180 select #intermediate_roleset.rolename, #intermediate_roleset.parentrolename, #intermediate_roleset.role_level 181 from #intermediate_roleset 182 where rolename not in (select rolename from #cumulative_role_set) 183 or parentrolename not in (select parentrolename 184 from #cumulative_role_set 185 where parentrolename is not null) 186 187 /* delete all rows from #intermediate_roleset */ 188 delete from #intermediate_roleset 189 190 /* copy all rows from #temp_role_set into #intermediate_roleset */ 191 /* Adaptive Server has expanded all '*' elements in the following statement */ insert into #intermediate_roleset 192 select #temp_role_set.rolename, #temp_role_set.parentrolename, #temp_role_set.role_level 193 from #temp_role_set 194 195 /* delete all rows from #temp_roleset */ 196 delete from #temp_role_set 197 end 198 199 /* select all rows from #cumulative_role_set and display it */ 200 201 select distinct "Role Name" = rolename, "Parent Role Name" = parentrolename, 202 "Level" = role_level 203 from #cumulative_role_set 204 order by role_level 205 206 return (0) 207 208
exec sp_procxmode 'sp_activeroles', 'AnyMode' go Grant Execute on sp_activeroles to public go
| RESULT SETS | |
| sp_activeroles_rset_002 | |
| sp_activeroles_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..sysloginroles (1)  read_writes table tempdb..#tmp_roleset (1) read_writes table tempdb..#intermediate_roleset (1) read_writes table tempdb..#cumulative_role_set (1) reads table master..sysattributes (1) reads table master..syssrvroles (1) reads table master..syslogins (1) read_writes table tempdb..#temp_role_set (1) |
