Database | Proc | Application | Created | Links |
sybsystemprocs | sp_dropexternlogin ![]() | ![]() | 14 déc. 14 | Defects Propagation Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */ 3 4 /* 5 ** Omni only 6 ** 7 ** Messages for "sp_dropexternlogin" 18319 8 ** 9 ** 17260, "Can't run '%1!' from within a transaction." 10 ** 18307, "Server name '%1!' does not exist in sysservers." 11 ** 18319, "There is no external login for server '%1!'." 12 ** 18320, "Only the 'sa' may drop another's external login." 13 ** 18321, "Remote login/alias dropped." 14 ** 17271, "'%1!' is the local server - remote login not applicable." 15 ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'" 16 ** 18778, "Unable to find login '%1!' with id '%2!' in syslogins." 17 ** 18780, "Synchronization will not occur because server '%1!' is the companion server." 18 ** 18782 "Unable to find a server with name '%1!' and id '%2!'." 19 */ 20 21 /* 22 ** IMPORTANT: Please read the following instructions before 23 ** making changes to this stored procedure. 24 ** 25 ** To make this stored procedure compatible with High Availability (HA), 26 ** changes to certain system tables must be propagated 27 ** to the companion server under some conditions. 28 ** The tables include (but are not limited to): 29 ** syslogins, sysservers, sysattributes, systimeranges, 30 ** sysresourcelimits, sysalternates, sysdatabases, 31 ** syslanguages, sysremotelogins, sysloginroles, 32 ** sysalternates (master DB only), systypes (master DB only), 33 ** sysusers (master DB only), sysprotects (master DB only) 34 ** please refer to the HA documentation for detail. 35 ** 36 ** Here is what you need to do: 37 ** For each insert/update/delete statement, add three sections to 38 ** -- start HA transaction prior to the statement 39 ** -- add the statement 40 ** -- add HA synchronization code to propagate the change to the companion 41 ** 42 ** For example, if you are adding 43 ** insert master.dbo.syslogins ...... 44 ** the code should look like: 45 ** 1. Before that SQL statement: 46 ** 47 ** 2. Now, the SQL statement: 48 ** insert master.dbo.syslogins ...... 49 ** 3. Add a HA synchronization section right after the SQL statement: 50 ** 51 ** 52 ** You may need to do similar change for each built-in function you 53 ** want to add. 54 ** 55 ** Finally, add a separate part at a place where it can not 56 ** be reached by the normal execution path: 57 ** clean_all: 58 ** 59 ** return (1) 60 */ 61 62 create or replace procedure sp_dropexternlogin 63 @server varchar(255), /* name of remote server */ 64 @loginame varchar(255) = null, /* user's local login */ 65 @rolename varchar(255) = null /* role name */ 66 as 67 begin 68 69 declare @msg varchar(1024), 70 @suid int, 71 @srid int, 72 @srvid smallint, 73 @dso_class smallint, 74 @attrib smallint, 75 @action smallint, 76 @dummy int, 77 @HA_CERTIFIED tinyint, 78 @retstat int, 79 @srvclass smallint, 80 @dbname sysname, 81 @log_for_rep int, 82 @nullarg char(1), 83 @status1 int, 84 @status2 int, 85 @gp_enabled int 86 87 88 select @srid = - 1, @retstat = 0, @HA_CERTIFIED = 0 89 select @status1 = 1 90 select @status2 = 1 91 92 93 94 /* 95 ** Check to see if we are using HA specific SP for a HA enabled 96 ** server. 97 */ 98 exec @retstat = sp_ha_check_certified 'sp_dropexternlogin', 99 @HA_CERTIFIED 100 if (@retstat != 0) 101 return (1) 102 103 /* 104 ** If we're in a transaction, disallow this since it might make 105 ** recovery impossible. 106 */ 107 if (@@trancount > 0) 108 begin 109 /* 110 ** 17260 Can't run '%1!' from within a transaction. 111 */ 112 raiserror 17260, "sp_dropexternlogin" 113 goto raise_error 114 end 115 116 set chained off 117 118 set transaction isolation level 1 119 120 /* 121 ** Validate the server name. 122 */ 123 select @srvid = srvid, @srvclass = srvclass 124 from master.dbo.sysservers 125 where srvname = @server 126 127 if (@@rowcount = 0) 128 begin 129 /* 130 ** 18307, "Server name '%1!' does not exist in sysservers." 131 */ 132 raiserror 18307, @server 133 goto raise_error 134 end 135 136 /* 137 ** This stored procedure should not be replicated when the server is 138 ** an HADR member. 139 */ 140 if exists (select * from master.dbo.sysservers 141 where (srvname = @server or srvname = @server + "DR") 142 and srvclass = (select number from master.dbo.spt_values 143 where type = 'X' and 144 lower(name) = lower("HADR_MEMBER"))) 145 begin 146 select @log_for_rep = 0 147 end 148 else 149 begin 150 select @log_for_rep = 1 151 end 152 153 /* 154 ** If @rolename was specified, ignore @loginame. 155 */ 156 if (@rolename is not null) 157 begin 158 select @loginame = null 159 160 /* 161 ** Check that the @rolename is valid. 162 */ 163 select @srid = srid from master.dbo.syssrvroles 164 where name = @rolename 165 166 if (@@rowcount = 0) 167 begin 168 /* 169 ** 18342, "Invalid name '%1!'. This role or user does 170 ** not exist in this SQL Server." 171 */ 172 raiserror 18342, @rolename 173 goto raise_error 174 end 175 end 176 177 if (@loginame is null) 178 begin 179 select @suid = - 1 180 end 181 else 182 begin 183 select @suid = suser_id(@loginame) 184 185 /* 186 ** Check that the @loginame is valid. 187 */ 188 if (@suid is null) 189 begin 190 /* 191 ** 18294 "User '%1!' is not a local user -- request 192 ** denied." 193 */ 194 raiserror 18294, @loginame 195 goto raise_error 196 end 197 end 198 199 /* 200 ** If granular permissions is not enabled then a user with sa_role 201 ** or sso_role can add extern logins for another user or for a role. 202 ** If granular permissions is enabled then the permission 203 ** 'manage any login' is required. proc_role and proc_auditperm will 204 ** also do auditing if required. Both will also print error message if 205 ** required. 206 */ 207 208 if (@suid != suser_id()) 209 begin -- { 210 select @nullarg = NULL 211 execute @status1 = sp_aux_checkroleperm "sa_role", 212 "manage any remote login", @nullarg, 213 @gp_enabled output 214 if (@status1 != 0) 215 begin 216 if (@gp_enabled = 0) 217 begin 218 execute @status2 = sp_aux_checkroleperm 219 "sso_role", @nullarg, @nullarg, 220 @gp_enabled output 221 if (@status2 != 0) 222 begin 223 select @dummy = proc_role("sa_role") 224 select @dummy = proc_role("sso_role") 225 return (1) 226 end 227 end 228 else 229 begin 230 select @dummy = proc_auditperm( 231 "manage any remote login", @status1) 232 return (1) 233 end 234 end 235 /* For Auditing */ 236 if (@gp_enabled = 0) 237 begin 238 if (@status1 = 0) 239 begin 240 if (proc_role("sa_role") = 0) 241 return (1) 242 end 243 if (@status2 = 0) 244 begin 245 if (proc_role("sso_role") = 0) 246 return (1) 247 end 248 end 249 else 250 begin 251 /* send audit record to the audit trail. */ 252 select @dummy = proc_auditperm( 253 "manage any remote login", @status1) 254 end 255 256 end -- } 257 258 259 260 begin tran rs_logexec 261 262 263 264 /* 265 ** Delete the remote login. 266 */ 267 if ((@srvclass != 12) and (@srvclass != 14) and (@srvclass != 15)) 268 select @dso_class = 9 269 else 270 select @dso_class = 21 271 272 select @attrib = 0 273 274 delete master.dbo.sysattributes 275 where class = @dso_class and attribute = @attrib and 276 object_info1 = @srvid and object = @suid and 277 (object_info2 = @srid or object_info2 is null) 278 /* 279 ** Check @@rowcount when it works 280 */ 281 if (@@rowcount > 0) 282 begin 283 select @action = 3 /* attribute drop */ 284 285 286 if (@log_for_rep = 1) 287 begin --{ 288 if (logexec(1) != 1) 289 begin 290 /* 291 ** 17756, "The execution of the stored procedure 292 ** '%1!' in database '%2!' was aborted because 293 ** there was an error in writing the replication 294 ** log record." 295 */ 296 select @dbname = db_name() 297 raiserror 17756, "sp_dropexternlogin", @dbname 298 299 select @retstat = 1 300 goto clean_all 301 end 302 end --} 303 304 commit tran rs_logexec 305 306 if (@@error != 0) 307 goto raise_error 308 309 /* 310 ** 18321, "Remote login/alias dropped" 311 */ 312 exec sp_getmessage 18321, @msg output 313 print @msg 314 315 /* 316 ** Sync the in-memory RDES with the new values 317 ** in sysattributes. 318 */ 319 if (attrib_notify(@dso_class, @attrib, "EL", @suid, @srvid, 320 @srid, null, null, null, null, null, null, null, 321 @action) = 0) 322 begin 323 select @retstat = 1 324 end 325 326 327 328 end 329 else 330 begin 331 /* 332 ** 18319, "There is no external login for server '%1!'." 333 */ 334 exec sp_getmessage 18319, @msg output 335 print @msg, @server 336 goto clean_all 337 end 338 339 return (@retstat) 340 341 clean_all: 342 rollback tran rs_logexec 343 344 raise_error: 345 346 347 return (1) 348 end 349
exec sp_procxmode 'sp_dropexternlogin', 'AnyMode' go Grant Execute on sp_dropexternlogin to public go
DEPENDENCIES |
PROCS AND TABLES USED reads table master..sysservers (1) ![]() calls proc sybsystemprocs..sp_ha_check_certified ![]() reads table tempdb..sysobjects (1) ![]() calls proc sybsystemprocs..sp_aux_checkroleperm ![]() reads table master..sysconfigures (1) ![]() reads table master..syscurconfigs (1) ![]() reads table master..syssrvroles (1) ![]() writes table master..sysattributes (1) ![]() reads table master..spt_values (1) ![]() calls proc sybsystemprocs..sp_getmessage ![]() reads table master..syslanguages (1) ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() reads table master..sysmessages (1) ![]() reads table sybsystemprocs..sysusermessages ![]() |