| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_addremotelogin | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */ 3 /* 4.8 1.1 06/14/90 sproc/src/addremotelogin */ 4 /* 5 ** Messages for "sp_addremotelogin" 17270 6 ** 7 ** 17240, "'%1!' is not a valid name." 8 ** 17260, "Can't run %1! from within a transaction." 9 ** 17270, "There is not a server named '%1!'." 10 ** 17271, "'%1!' is the local server - remote login not applicable." 11 ** 17272, "There is already a default-name mapping of a remote login from remote server '%1!'." 12 ** 17273, "New remote login created." 13 ** 17274, "'%1!' isn't a local user -- remote login denied." 14 ** 17275, "There is already a remote user named '%1!' for remote server '%2!'." 15 ** 17276 "Usage: sp_addremotelogin remoteserver [, loginame [,remotename]]" 16 ** 17869, "Stored procedure %1 failed because %2 failed in database %3." 17 ** 18388, "You must be in the master database in order to run '%1'!." 18 ** 18409, "The built-in function getdbrepstat() failed." 19 ** 18773, "HA_LOG: HA consistency check failure in stored procedure '%1!' on companion server '%2!'" 20 ** 18780, "Synchronization will not occur because server '%1!' is the companion server." 21 ** 18782, "Unable to find a server with name '%1!' and id '%2!'." 22 ** 18778, "Unable to find login '%1!' with id '%2!' in syslogins." 23 */ 24 25 /* 26 ** IMPORTANT: Please read the following instructions before 27 ** making changes to this stored procedure. 28 ** 29 ** To make this stored procedure compatible with High Availability (HA), 30 ** changes to certain system tables must be propagated 31 ** to the companion server under some conditions. 32 ** The tables include (but are not limited to): 33 ** syslogins, sysservers, sysattributes, systimeranges, 34 ** sysresourcelimits, sysalternates, sysdatabases, 35 ** syslanguages, sysremotelogins, sysloginroles, 36 ** sysalternates (master DB only), systypes (master DB only), 37 ** sysusers (master DB only), sysprotects (master DB only) 38 ** please refer to the HA documentation for detail. 39 ** 40 ** Here is what you need to do: 41 ** For each insert/update/delete statement, add three sections to 42 ** -- start HA transaction prior to the statement 43 ** -- add the statement 44 ** -- add HA synchronization code to propagate the change to the companion 45 ** 46 ** For example, if you are adding 47 ** insert master.dbo.syslogins ...... 48 ** the code should look like: 49 ** 1. Now, the SQL statement: 50 ** insert master.dbo.syslogins ...... 51 ** 2. Add a HA synchronization section right after the SQL statement: 52 ** 53 ** 54 ** You may need to do similar change for each built-in function you 55 ** want to add. 56 */ 57 58 create procedure sp_addremotelogin 59 @remoteserver varchar(255), /* name of remote server */ 60 @loginame varchar(255) = NULL, /* user's remote name */ 61 @remotename varchar(255) = NULL /* user's local user name */ 62 as 63 64 declare @msg varchar(1024) 65 declare @name varchar(255) 66 declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */ 67 declare @maxlen int 68 declare @retstat int 69 declare @dummy int 70 declare @dbname varchar(255) /* for logexec error message */ 71 declare @master_is_rep int /* whether master db is replicated */ 72 73 74 select @HA_CERTIFIED = 0 75 76 77 78 79 /* check to see if we are using HA specific SP for a HA enabled server */ 80 exec @retstat = sp_ha_check_certified 'sp_addremotelogin', @HA_CERTIFIED 81 if (@retstat != 0) 82 return (1) 83 84 /* 85 ** If we are logging this system procedure for replication, 86 ** we must be in the 'master' database to avoid creating a 87 ** multi-database transaction which could make recovery of 88 ** the 'master' database impossible. 89 */ 90 select @master_is_rep = getdbrepstat(1) 91 92 if (@master_is_rep < 0) 93 begin 94 /* 95 ** 18409, "The built-in function getdbrepstat() failed." 96 */ 97 raiserror 18409, "getdbrepstat" 98 return (1) 99 end 100 101 select @dbname = db_name() 102 103 if (@master_is_rep > 0) and (@dbname != "master") 104 begin 105 /* 106 ** 18388, "You must be in the master database in order 107 ** to run '%1!'." 108 */ 109 raiserror 18388, "sp_addremotelogin" 110 return (1) 111 end 112 113 /* 114 ** If we're in a transaction, disallow this since it might make recovery 115 ** impossible. 116 */ 117 if @@trancount > 0 118 begin 119 /* 120 ** 17260, "Can't run %1! from within a transaction." 121 */ 122 raiserror 17260, "sp_addremotelogin" 123 return (1) 124 end 125 else 126 begin 127 set chained off 128 end 129 set transaction isolation level 1 130 131 /* check if user has sa role or sso role. proc_role also generates an 132 ** audit record and prints an error message for sa role if both sa role 133 ** and sso role are not present. 134 */ 135 136 137 if ((charindex("sa_role", show_role()) = 0) AND (charindex("sso_role", show_role()) = 0)) 138 begin 139 select @dummy = proc_role("sa_role") /* to perform auditing if sa_role fails */ 140 raiserror 17888, "addremotelogin" 141 return (1) 142 end 143 144 /* 145 ** Check that the server name is valid. 146 */ 147 if not exists (select * 148 from master.dbo.sysservers 149 where srvname = @remoteserver) 150 begin 151 /* 152 ** 17270, "There is not a server named '%1!'." 153 */ 154 raiserror 17270, @remoteserver 155 return (1) 156 end 157 158 /* 159 ** If it's the local server don't bother. 160 */ 161 if exists (select * 162 from master.dbo.sysservers 163 where srvname = @remoteserver 164 and srvid = 0) 165 begin 166 /* 167 ** 17271, "'%1!' is the local server - remote login not applicable." 168 */ 169 raiserror 17271, @remoteserver 170 return (1) 171 end 172 173 /* 174 ** This transaction also writes a log record for replicating the 175 ** invocation of this procedure. If logexec() fails, the transaction 176 ** is aborted. 177 ** 178 ** IMPORTANT: The name rs_logexec is significant and is used by 179 ** Adaptive Server. 180 */ 181 begin tran rs_logexec 182 183 184 185 /* 186 ** There are three cases to handle. 187 ** 188 ** 1) if only @remoteserver is given then a entry is made in 189 ** sysremotelogins that means anyone that doesn't have 190 ** an exact of mapped match in sysremotelogins will use 191 ** their remotename as their local name and it will be looked 192 ** up in syslogins. 193 ** 194 ** 2) if @remotename is omitted then it means that anyone from the 195 ** remote server logging in that doesn't have a complete 196 ** match in sysremotelogins will be mapped to @loginame. 197 ** 198 ** 3) if @remotename and @loginame are given then it is a straight 199 ** remote login for sysremotelogins. 200 */ 201 202 /* 203 ** Case 1: Only @remoteserver given. 204 */ 205 if @loginame is null and @remotename is null 206 begin 207 /* 208 ** Check that there is not already an entry for local mapping. 209 */ 210 if exists (select * 211 from master.dbo.sysremotelogins l, master.dbo.sysservers s 212 where l.remoteserverid = s.srvid 213 and s.srvname = @remoteserver 214 and l.remoteusername is null) 215 begin 216 /* 217 ** 17272, "There is already a default-name mapping of a remote login from remote server '%1!'." 218 */ 219 raiserror 17272, @remoteserver 220 goto clean_all 221 end 222 223 224 225 /* 226 ** Add the entry. 227 */ 228 insert into master.dbo.sysremotelogins 229 (remoteserverid, remoteusername, suid, status) 230 select srvid, null, - 1, 0 231 from master.dbo.sysservers 232 where srvname = @remoteserver 233 234 235 236 /* 237 ** 17273, "New remote login created." 238 */ 239 exec sp_getmessage 17273, @msg output 240 print @msg 241 242 goto ha_syn 243 end 244 245 /* 246 ** Check that the @loginame is valid. These is needed for both 247 ** case 2 and 3. 248 */ 249 if not exists (select * 250 from master.dbo.syslogins 251 where name = @loginame and 252 ((status & 512) != 512)) /* not LOGIN PROFILE */ 253 begin 254 /* 255 ** 17274, "'%1!' isn't a local user -- remote login denied." 256 */ 257 raiserror 17274, @loginame 258 goto clean_all 259 end 260 261 /* 262 ** Check to make sure that there is not already a @remotename for 263 ** the @remoteserver. 264 */ 265 if (@remotename is not null) 266 begin 267 select @maxlen = length from master.dbo.syscolumns 268 where id = object_id("master.dbo.sysremotelogins") and 269 name = "remoteusername" 270 271 if char_length(@remotename) > @maxlen 272 begin 273 /* 274 ** 17240, "'%1!' is not a valid name." 275 */ 276 raiserror 17240, @remotename 277 goto clean_all 278 end 279 end 280 281 if exists (select * 282 from master.dbo.sysremotelogins l, master.dbo.sysservers s 283 where l.remoteserverid = s.srvid 284 and s.srvname = @remoteserver 285 and l.remoteusername = @remotename) 286 begin 287 /* 288 ** 17275, "There is already a remote user named '%1!' for remote server '%2!'." 289 */ 290 select @name = isnull(@remotename, "NULL") 291 raiserror 17275, @name, @remoteserver 292 goto clean_all 293 end 294 295 296 297 /* 298 ** Case 2: We want to make an entry into sysremotelogins that will map 299 ** any non-exact matches to a particular local user. 300 */ 301 if @remotename is null 302 begin 303 /* 304 ** Check that there is not already an entry for local mapping. 305 */ 306 if exists (select * 307 from master.dbo.sysremotelogins l, master.dbo.sysservers s 308 where l.remoteserverid = s.srvid 309 and s.srvname = @remoteserver 310 and l.remoteusername is null) 311 begin 312 /* 313 ** 17272, "There is already a default-name mapping of a remote login from remote server '%1!'." 314 */ 315 raiserror 17272, @remoteserver 316 goto clean_all 317 end 318 319 320 321 322 /* 323 ** Go ahead and make the entry. 324 */ 325 insert into master.dbo.sysremotelogins 326 (remoteserverid, remoteusername, suid, status) 327 select srvid, null, suser_id(@loginame), 0 328 from master.dbo.sysservers 329 where srvname = @remoteserver 330 331 332 /* 333 ** 17273, "New remote login created." 334 */ 335 exec sp_getmessage 17273, @msg output 336 print @msg 337 338 goto ha_syn 339 end 340 341 /* 342 ** Case 3: All the parameters have been supplied. All we need to check 343 ** is that the entry isn't already in sysremotelogins. 344 ** We've verified the @remoteserver and @loginame above. 345 */ 346 if @loginame is not null and @remotename is not null 347 begin 348 /* 349 ** Make sure that the @loginame and @remotename are a 350 ** unique combination. 351 */ 352 if exists (select * 353 from master.dbo.sysremotelogins l, master.dbo.sysservers s 354 where l.remoteusername = @remotename 355 and l.remoteserverid = s.srvid 356 and s.srvname = @remoteserver 357 and l.suid = suser_id(@loginame)) 358 begin 359 /* 360 ** 17275, "There is already a remote user named '%1!' for remote server '%2!'." 361 */ 362 raiserror 17275, @remotename, @remoteserver 363 goto clean_all 364 end 365 366 367 368 /* 369 ** Go ahead and do the insert. 370 */ 371 insert into master.dbo.sysremotelogins 372 (remoteserverid, remoteusername, suid, status) 373 select srvid, @remotename, suser_id(@loginame), 0 374 from master.dbo.sysservers 375 where srvname = @remoteserver 376 377 378 379 /* 380 ** 17273, "New remote login created." 381 */ 382 exec sp_getmessage 17273, @msg output 383 print @msg 384 385 goto ha_syn 386 end 387 388 /* 389 ** We got here because the syntax was incorrect. 390 */ 391 392 raiserror 17276 393 goto clean_all 394 395 ha_syn: 396 397 398 399 /* 400 ** log the command for replication support 401 */ 402 if (@master_is_rep > 0) 403 begin 404 if (logexec(1) != 1) 405 begin 406 /* 407 ** , "17869 Stored procedure %1 failed' 408 ** because '%2' failed in database '%3'." 409 */ 410 raiserror 17869, "sp_addremotelogin", "logexec()", @dbname 411 goto clean_all 412 end 413 end 414 415 commit tran rs_logexec 416 417 return (0) 418 419 420 clean_all: 421 rollback tran rs_logexec 422 return (1) 423
exec sp_procxmode 'sp_addremotelogin', 'AnyMode' go Grant Execute on sp_addremotelogin to public go
| DEFECTS | |
 MINU 4 Unique Index with nullable columns master..sysremotelogins | master..sysremotelogins |
| MTYP 4 Assignment type mismatch remoteusername: varchar(30) = varchar(255) | 373 |
| QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysremotelogins.csysremotelogins unique clustered (remoteserverid, remoteusername) Intersection: {remoteusername} | 214 |
| QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysremotelogins.csysremotelogins unique clustered (remoteserverid, remoteusername) Intersection: {remoteusername} | 285 |
| QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysremotelogins.csysremotelogins unique clustered (remoteserverid, remoteusername) Intersection: {remoteusername} | 310 |
| QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysremotelogins.csysremotelogins unique clustered (remoteserverid, remoteusername) Intersection: {remoteusername} | 354 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 164 |
 MGTP 3 Grant to public master..syscolumns | |
| MGTP 3 Grant to public master..syslogins | |
| MGTP 3 Grant to public master..sysremotelogins | |
| MGTP 3 Grant to public master..sysservers | |
| MGTP 3 Grant to public sybsystemprocs..sp_addremotelogin | |
| MNER 3 No Error Check should check @@error after insert | 228 |
| MNER 3 No Error Check should check return value of exec | 239 |
| MNER 3 No Error Check should check @@error after insert | 325 |
| MNER 3 No Error Check should check return value of exec | 335 |
| MNER 3 No Error Check should check @@error after insert | 371 |
| MNER 3 No Error Check should check return value of exec | 382 |
| MUCO 3 Useless Code Useless Brackets | 81 |
| MUCO 3 Useless Code Useless Brackets | 82 |
| MUCO 3 Useless Code Useless Brackets | 92 |
| MUCO 3 Useless Code Useless Brackets | 98 |
| MUCO 3 Useless Code Useless Brackets | 110 |
| MUCO 3 Useless Code Useless Brackets | 123 |
| MUCO 3 Useless Code Useless Brackets | 137 |
| MUCO 3 Useless Code Useless Brackets | 141 |
| MUCO 3 Useless Code Useless Brackets | 155 |
| MUCO 3 Useless Code Useless Brackets | 170 |
| MUCO 3 Useless Code Useless Brackets | 265 |
| MUCO 3 Useless Code Useless Brackets | 402 |
| MUCO 3 Useless Code Useless Brackets | 404 |
| MUCO 3 Useless Code Useless Brackets | 417 |
| MUCO 3 Useless Code Useless Brackets | 422 |
| QAFM 3 Var Assignment from potentially many rows | 267 |
| QISO 3 Set isolation level | 129 |
| QNAJ 3 Not using ANSI Inner Join | 211 |
| QNAJ 3 Not using ANSI Inner Join | 282 |
| QNAJ 3 Not using ANSI Inner Join | 307 |
| QNAJ 3 Not using ANSI Inner Join | 353 |
| QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {id} | 268 |
| VNRD 3 Variable is not read @dummy | 139 |
 MSUB 2 Subquery Marker | 147 |
| MSUB 2 Subquery Marker | 161 |
| MSUB 2 Subquery Marker | 210 |
| MSUB 2 Subquery Marker | 249 |
| MSUB 2 Subquery Marker | 281 |
| MSUB 2 Subquery Marker | 306 |
| MSUB 2 Subquery Marker | 352 |
| MTR1 2 Metrics: Comments Ratio Comments: 58% | 58 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 30 = 35dec - 7exi + 2 | 58 |
| MTR3 2 Metrics: Query Complexity Complexity: 149 | 58 |
| PRED_QUERY_COLLECTION 2 {r=master..sysremotelogins, s=master..sysservers} 0 | 210 |
| PRED_QUERY_COLLECTION 2 {r=master..sysremotelogins, s=master..sysservers} 0 | 281 |
| PRED_QUERY_COLLECTION 2 {r=master..sysremotelogins, s=master..sysservers} 0 | 306 |
| PRED_QUERY_COLLECTION 2 {r=master..sysremotelogins, s=master..sysservers} 0 | 352 |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..syslogins (1)  calls proc sybsystemprocs..sp_getmessage  calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages reads table master..sysmessages (1) read_writes table master..sysremotelogins (1) reads table master..syscolumns (1) reads table master..sysservers (1) calls proc sybsystemprocs..sp_ha_check_certified  reads table tempdb..sysobjects (1)  |