| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_setpsexe | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G% */ 3 4 /* 5 ** Messages for "sp_setpsexe" 6 ** 17260, "Can't run %1! from within a transaction." 7 ** 18249, "Name '%1!' is not valid." 8 ** 18262, "A non-SA user can not modify attributes of another task." 9 ** 18274, "A non-SA user can only lower its own priority value." 10 ** 18264, "No SQL Server process with the specified ID exists." 11 ** 18266, "'%1!' is not a valid execution attribute" 12 ** 18252, "'%1!' value '%2!' is not valid." 13 ** 18275, "Failed to set attribute '%1!' to '%2!' for spid '%3!'. Check 14 ** server errorlog for any additional information." 15 ** 18367, "The specified thread pool does not exist." 16 */ 17 18 create procedure sp_setpsexe 19 @spid int, /* id of SQL Server process */ 20 @exeattr varchar(12), /* name of execution attribute */ 21 @value varchar(30) /* value assigned to attribute */ 22 as 23 24 declare @attrib_id int, 25 @user_id int, 26 @action int, 27 @my_suid int, 28 @object_type varchar(2), 29 @dummy int, 30 @curvalue varchar(30), 31 @oldvalue varchar(255), 32 @loc int, 33 @role int, 34 @upcase_str varchar(30), 35 @kernel_mode varchar(30) 36 37 select @kernel_mode = @@kernelmode 38 select @action = 1 /* add operation */ 39 select @user_id = NULL /* Id of user from Syslogins table */ 40 select @dummy = 0 41 select @oldvalue = NULL 42 43 /* 44 ** IF we're in a transaction, disallow this since it might make recovery 45 ** impossible. 46 */ 47 IF @@trancount > 0 48 BEGIN 49 /* 50 ** 17260, "Can't run %1! from within a transaction." 51 */ 52 raiserror 17260, "sp_setpsexe" 53 return (1) 54 END 55 ELSE 56 BEGIN 57 /* Use TSQL mode of unchained transactions */ 58 set chained off 59 END 60 61 /* Dont do "Dirty Reads" */ 62 set transaction isolation level 1 63 64 65 /* convert system defined values to upper case */ 66 select @upcase_str = upper(@value) 67 IF ((@upcase_str = "HIGH") OR (@upcase_str = "MEDIUM") OR 68 (@upcase_str = "LOW") OR (@upcase_str = "ANYENGINE") OR 69 (@upcase_str = "LASTONLINE")) 70 select @value = @upcase_str 71 72 /* Find out if the caller has sa_role. Note that 'sa_role' 73 ** string may be embedded in a user defined role name and 74 ** users with these roles should be checked for permissions. 75 */ 76 select @role = 1 77 select @loc = charindex("sa_role", show_role()) 78 IF (@loc > 0) 79 BEGIN 80 IF (@loc != 1) 81 BEGIN 82 /* check for space just before 'sa_role' string */ 83 IF (ascii(substring(show_role(), (@loc - 1), 8)) != 32) 84 select @role = 0 85 END 86 87 /* check if role string ends in space */ 88 IF ((@role = 1) AND 89 (char_length(substring(show_role(), @loc, 90 (char_length(show_role()) - (@loc - 1)))) != 7) AND 91 (ascii(substring(show_role(), (@loc + 7), 1)) != 32)) 92 93 select @role = 0 94 END 95 ELSE 96 select @role = 0 97 98 /* check that user has sa role or modifying self */ 99 IF (@role = 0) 100 BEGIN 101 select @my_suid = (select suid from master..sysprocesses 102 where spid = @spid) 103 select @curvalue = (select priority from master..sysprocesses 104 where spid = @spid) 105 IF (@my_suid != suser_id()) 106 BEGIN 107 /* 108 ** 18262, "A non-SA user can not modify attributes of another 109 ** process." 110 */ 111 raiserror 18262 112 return (1) 113 END 114 ELSE IF @exeattr != "priority" 115 BEGIN 116 /* 117 ** 18263, "A non-SA user can only modify its priority value" 118 */ 119 raiserror 18263 120 return (1) 121 END 122 ELSE IF (@curvalue != "HIGH") 123 BEGIN 124 IF (((@curvalue = "MEDIUM") AND (@value = "HIGH")) OR 125 ((@curvalue = "LOW") AND (@value = "HIGH")) OR 126 ((@curvalue = "LOW") AND (@value = "MEDIUM"))) 127 BEGIN 128 /* 129 ** 18274, "A non-SA user can only lower its priority 130 ** value" 131 */ 132 raiserror 18274 133 return (1) 134 END 135 END 136 END 137 ELSE 138 /* validate that this guy is really the sa */ 139 IF (proc_role("sa_role") = 0) 140 return 1 141 142 /* 143 ** Check to see that the input params are correct 144 */ 145 /* Check that process exists */ 146 IF @spid not in (select spid from master..sysprocesses) 147 BEGIN 148 /* 149 ** 18264,"No SQL Server process with specified id exists." 150 */ 151 raiserror 18264 152 return (1) 153 END 154 155 /* check that @exeattr value specified is valid */ 156 IF ((@exeattr != 'priority') 157 AND (@exeattr != 'timeslice') 158 AND (((@kernel_mode = 'process') AND (@exeattr != 'enginegroup')) 159 OR ((@kernel_mode = 'threaded') AND (@exeattr != 'threadpool')))) 160 BEGIN 161 /* 162 ** 18266, "'%1!' is not a valid execution attribute" 163 */ 164 raiserror 18266, @exeattr 165 return (1) 166 END 167 168 /* check the priority value is correct */ 169 IF (@exeattr = "priority") 170 BEGIN 171 IF ((@value != "HIGH") AND 172 (@value != "MEDIUM") AND 173 (@value != "LOW")) 174 BEGIN 175 /* 176 ** 18252, "'%1!' value '%2!' is not valid." 177 */ 178 raiserror 18252, @exeattr, @value 179 return (1) 180 END 181 END 182 183 /* check that enginegroup value is ok if in process mode */ 184 IF ((@kernel_mode = 'process') AND (@exeattr = "enginegroup") AND 185 (@value != "ANYENGINE") AND (@value != "LASTONLINE")) 186 BEGIN 187 IF not exists (select * from master..sysattributes where 188 class = 6 AND 189 attribute = 1 AND 190 object_type = 'EG' AND 191 object_cinfo = @value) 192 193 BEGIN 194 /* 195 ** 18252, "'%1!' value '%2!' is not valid." 196 */ 197 raiserror 18252, @exeattr, @value 198 return (1) 199 END 200 END 201 /* For THREADED kernel mode, check for valid threadpool */ 202 ELSE IF ((@kernel_mode != "process") AND (@exeattr = "threadpool")) 203 BEGIN 204 IF valid_name(@value, 30) = 0 205 BEGIN 206 /* 207 ** 18249, "Name '%1!' is not valid." 208 */ 209 raiserror 18249, @value 210 return (1) 211 END 212 /* 213 ** Does the threadpool exist? 214 */ 215 if not exists (select * 216 from master.dbo.monThreadPool 217 where ThreadPoolName = @value) 218 BEGIN 219 /* 18367, "The specified thread pool does not exist." */ 220 raiserror 18367 221 return (1) 222 END 223 END 224 225 226 227 /* 228 ** Now hook up with Sysattributes Table... 229 */ 230 231 select @attrib_id = 3 232 select @object_type = "PS" 233 BEGIN 234 select @oldvalue = (select char_value from master..sysattributes where 235 class = 6 AND 236 attribute = @attrib_id AND 237 object_type = @object_type AND 238 object_info1 = @spid AND 239 object_cinfo = @exeattr) 240 IF (@oldvalue is not NULL) 241 BEGIN 242 update master..sysattributes 243 set char_value = @value 244 where class = 6 AND 245 attribute = @attrib_id AND 246 object_type = @object_type AND 247 object_info1 = @spid AND 248 object_cinfo = @exeattr 249 END 250 ELSE 251 BEGIN 252 insert master..sysattributes 253 (class, attribute, object_type, object_info1, 254 object_cinfo, char_value) 255 values (6, @attrib_id, @object_type, @spid, 256 @exeattr, @value) 257 END 258 259 IF attrib_notify(6, @attrib_id, @object_type, NULL, @spid, 260 NULL, NULL, @exeattr, NULL, @value, 261 NULL, NULL, "", @action) = 0 262 BEGIN 263 /* 264 ** 18275, "Failed to set attribute '%1!' to '%2!' for spid 265 ** '%3!'. Check server errorlog for any additional 266 ** information." 267 */ 268 IF (@oldvalue is not NULL) 269 update master..sysattributes 270 set char_value = @oldvalue 271 where class = 6 AND 272 attribute = @attrib_id AND 273 object_type = @object_type AND 274 object_info1 = @spid AND 275 object_cinfo = @exeattr 276 ELSE 277 delete master..sysattributes 278 where class = 6 AND 279 attribute = @attrib_id AND 280 object_type = @object_type AND 281 object_info1 = @spid AND 282 object_cinfo = @exeattr 283 284 raiserror 18275, @exeattr, @value, @spid 285 return (1) 286 END 287 END 288 289 return (0) 290
exec sp_procxmode 'sp_setpsexe', 'AnyMode' go Grant Execute on sp_setpsexe to public go
| DEFECTS | |
 MEST 4 Empty String will be replaced by Single Space | 261 |
| MINU 4 Unique Index with nullable columns master..sysattributes | master..sysattributes |
| MTYP 4 Assignment type mismatch @kernel_mode: varchar(30) = int | 37 |
| MTYP 4 Assignment type mismatch attribute: smallint = int | 255 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 102 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 104 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 188 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 189 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 235 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 236 |
| QTYP 4 Comparison type mismatch smallint = int | 236 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 244 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 245 |
| QTYP 4 Comparison type mismatch smallint = int | 245 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 271 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 272 |
| QTYP 4 Comparison type mismatch smallint = int | 272 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 278 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 279 |
| QTYP 4 Comparison type mismatch smallint = int | 279 |
| TNOI 4 Table with no index master..monThreadPool | master..monThreadPool |
| TNOI 4 Table with no index master..sysprocesses | master..sysprocesses |
 MGTP 3 Grant to public master..monThreadPool | |
| MGTP 3 Grant to public master..sysattributes | |
| MGTP 3 Grant to public master..sysprocesses | |
| MGTP 3 Grant to public sybsystemprocs..sp_setpsexe | |
| MNER 3 No Error Check should check @@error after update | 242 |
| MNER 3 No Error Check should check @@error after insert | 252 |
| MNER 3 No Error Check should check @@error after update | 269 |
| MNER 3 No Error Check should check @@error after delete | 277 |
| MUCO 3 Useless Code Useless Brackets | 53 |
| MUCO 3 Useless Code Useless Brackets | 67 |
| MUCO 3 Useless Code Useless Brackets | 78 |
| MUCO 3 Useless Code Useless Brackets | 80 |
| MUCO 3 Useless Code Useless Brackets | 83 |
| MUCO 3 Useless Code Useless Brackets | 88 |
| MUCO 3 Useless Code Useless Brackets | 99 |
| MUCO 3 Useless Code Useless Brackets | 105 |
| MUCO 3 Useless Code Useless Brackets | 112 |
| MUCO 3 Useless Code Useless Brackets | 120 |
| MUCO 3 Useless Code Useless Brackets | 122 |
| MUCO 3 Useless Code Useless Brackets | 124 |
| MUCO 3 Useless Code Useless Brackets | 133 |
| MUCO 3 Useless Code Useless Brackets | 139 |
| MUCO 3 Useless Code Useless Brackets | 152 |
| MUCO 3 Useless Code Useless Brackets | 156 |
| MUCO 3 Useless Code Useless Brackets | 165 |
| MUCO 3 Useless Code Useless Brackets | 169 |
| MUCO 3 Useless Code Useless Brackets | 171 |
| MUCO 3 Useless Code Useless Brackets | 179 |
| MUCO 3 Useless Code Useless Brackets | 184 |
| MUCO 3 Useless Code Useless Brackets | 198 |
| MUCO 3 Useless Code Useless Brackets | 202 |
| MUCO 3 Useless Code Useless Brackets | 210 |
| MUCO 3 Useless Code Useless Brackets | 221 |
| MUCO 3 Useless Code Useless Begin-End Pair | 233 |
| MUCO 3 Useless Code Useless Brackets | 240 |
| MUCO 3 Useless Code Useless Brackets | 268 |
| MUCO 3 Useless Code Useless Brackets | 285 |
| MUCO 3 Useless Code Useless Brackets | 289 |
| MUOT 3 Updates outside transaction | 277 |
| QAPT 3 Access to Proxy Table master..monThreadPool | 216 |
| QISO 3 Set isolation level | 62 |
| QIWC 3 Insert with not all columns specified missing 9 columns out of 15 | 253 |
| 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_type, object_cinfo, attribute, class} | 188 |
| 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_info1, object_type, object_cinfo, attribute, class} | 235 |
| 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_info1, object_type, object_cinfo, attribute, class} | 244 |
| 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_info1, object_type, object_cinfo, attribute, class} | 271 |
| 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_info1, object_type, object_cinfo, attribute, class} | 278 |
| VNRD 3 Variable is not read @user_id | 39 |
| VNRD 3 Variable is not read @dummy | 40 |
 MSUB 2 Subquery Marker | 101 |
| MSUB 2 Subquery Marker | 103 |
| MSUB 2 Subquery Marker | 187 |
| MSUB 2 Subquery Marker | 215 |
| MSUB 2 Subquery Marker | 234 |
| MTR1 2 Metrics: Comments Ratio Comments: 35% | 18 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 42 = 53dec - 13exi + 2 | 18 |
| MTR3 2 Metrics: Query Complexity Complexity: 156 | 18 |
| DEPENDENCIES |
| PROCS AND TABLES USED read_writes table master..sysattributes (1)  reads table master..monThreadPool (1) reads table master..sysprocesses (1) |