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 | |
![]() | 261 |
![]() | master..sysattributes |
![]() | 37 |
![]() | 255 |
![]() | 102 |
![]() | 104 |
![]() | 188 |
![]() | 189 |
![]() | 235 |
![]() | 236 |
![]() | 236 |
![]() | 244 |
![]() | 245 |
![]() | 245 |
![]() | 271 |
![]() | 272 |
![]() | 272 |
![]() | 278 |
![]() | 279 |
![]() | 279 |
![]() | master..monThreadPool |
![]() | master..sysprocesses |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | 242 |
![]() | 252 |
![]() | 269 |
![]() | 277 |
![]() | 53 |
![]() | 67 |
![]() | 78 |
![]() | 80 |
![]() | 83 |
![]() | 88 |
![]() | 99 |
![]() | 105 |
![]() | 112 |
![]() | 120 |
![]() | 122 |
![]() | 124 |
![]() | 133 |
![]() | 139 |
![]() | 152 |
![]() | 156 |
![]() | 165 |
![]() | 169 |
![]() | 171 |
![]() | 179 |
![]() | 184 |
![]() | 198 |
![]() | 202 |
![]() | 210 |
![]() | 221 |
![]() | 233 |
![]() | 240 |
![]() | 268 |
![]() | 285 |
![]() | 289 |
![]() | 277 |
![]() | 216 |
![]() | 62 |
![]() | 253 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object_cinfo, attribute, class} | 188 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_info1, object_type, object_cinfo, attribute, class} | 235 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_info1, object_type, object_cinfo, attribute, class} | 244 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_info1, object_type, object_cinfo, attribute, class} | 271 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_info1, object_type, object_cinfo, attribute, class} | 278 |
![]() | 39 |
![]() | 40 |
![]() | 101 |
![]() | 103 |
![]() | 187 |
![]() | 215 |
![]() | 234 |
![]() | 18 |
![]() | 18 |
![]() | 18 |
DEPENDENCIES |
PROCS AND TABLES USED read_writes table master..sysattributes (1) ![]() reads table master..monThreadPool (1) ![]() reads table master..sysprocesses (1) ![]() |