| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_dropremotelogin | | 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/defaultlanguage */ 4 5 /* 6 ** Messages for "sp_dropremotelogin" 17512 7 ** 8 ** 17260, "Can't run %1! from within a transaction." 9 ** 17512, "Remote login dropped." 10 ** 17513, "There is no remote user '%1!' mapped to local user '%2!' from the remote server '%3!'." 11 ** 17869, "Stored procedure %1 failed because %2 failed in database %3." 12 ** 18388, "You must be in the master database in order to run '%1'!." 13 ** 18409, "The built-in function getdbrepstat() failed." 14 ** 18773, "HA_LOG: HA consistency check failure in stored procedure '%1!' on companion server '%2!'" 15 ** 18780, "Synchronization will not occur because server '%1!' is the companion server." 16 ** 18782, "Unable to find a server with name '%1!' and id '%2!'." 17 ** 18778, "Unable to find login '%1!' with id '%2!' in syslogins." 18 */ 19 20 /* 21 ** IMPORTANT: Please read the following instructions before 22 ** making changes to this stored procedure. 23 ** 24 ** To make this stored procedure compatible with High Availability (HA), 25 ** changes to certain system tables must be propagated 26 ** to the companion server under some conditions. 27 ** The tables include (but are not limited to): 28 ** syslogins, sysservers, sysattributes, systimeranges, 29 ** sysresourcelimits, sysalternates, sysdatabases, 30 ** syslanguages, sysremotelogins, sysloginroles, 31 ** sysalternates (master DB only), systypes (master DB only), 32 ** sysusers (master DB only), sysprotects (master DB only) 33 ** please refer to the HA documentation for detail. 34 ** 35 ** Here is what you need to do: 36 ** For each insert/update/delete statement, add three sections to 37 ** -- start HA transaction prior to the statement 38 ** -- add the statement 39 ** -- add HA synchronization code to propagate the change to the companion 40 ** 41 ** For example, if you are adding 42 ** insert master.dbo.syslogins ...... 43 ** the code should look like: 44 ** 1. Now, the SQL statement: 45 ** insert master.dbo.syslogins ...... 46 ** 2. Add a HA synchronization section right after the SQL statement: 47 ** 48 ** 49 ** You may need to do similar change for each built-in function you 50 ** want to add. 51 */ 52 53 create procedure sp_dropremotelogin 54 @remoteserver varchar(255), /* name of remote server */ 55 @loginame varchar(255) = NULL, /* user's remote name */ 56 @remotename varchar(255) = NULL /* user's local user name */ 57 as 58 59 declare @msg varchar(1024) 60 declare @suid int 61 declare @rname varchar(255) 62 declare @lname varchar(255) 63 declare @rtn_code int 64 declare @rowcount_save int 65 declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */ 66 declare @retstat int 67 declare @dummy int 68 declare @dbname varchar(255) /* for logexec error message */ 69 declare @master_is_rep int /* whether master db is replicated */ 70 71 72 select @HA_CERTIFIED = 0 73 74 75 76 77 /* 78 ** If we are logging this system procedure for replication, 79 ** we must be in the 'master' database to avoid creating a 80 ** multi-database transaction which could make recovery of 81 ** the 'master' database impossible. 82 */ 83 select @master_is_rep = getdbrepstat(1) 84 85 if (@master_is_rep < 0) 86 begin 87 /* 88 ** 18409, "The built-in function getdbrepstat() failed." 89 */ 90 raiserror 18409, "getdbrepstat" 91 return (1) 92 end 93 94 select @dbname = db_name() 95 96 if (@master_is_rep > 0) and (@dbname != "master") 97 begin 98 /* 99 ** 18388, "You must be in the master database in order 100 ** to run '%1!'." 101 */ 102 raiserror 18388, "sp_dropremotelogin" 103 return (1) 104 end 105 106 /* 107 ** If we're in a transaction, disallow this since it might make recovery 108 ** impossible. 109 */ 110 if @@trancount > 0 111 begin 112 /* 113 ** 17260, "Can't run %1! from within a transaction." 114 */ 115 raiserror 17260, "sp_dropremotelogin" 116 return (1) 117 end 118 else 119 begin 120 set chained off 121 end 122 123 set transaction isolation level 1 124 125 /* check if user has sa role or sso_role,proc_role also generates an 126 ** audit record and prints an error message for sa role if both sa role 127 ** and sso role are not present. 128 */ 129 130 131 if ((charindex("sa_role", show_role()) = 0) AND (charindex("sso_role", show_role()) = 0)) 132 begin 133 select @dummy = proc_role("sa_role") /* to perform auditing if sa_role fails */ 134 raiserror 17888, "dropremotelogin" 135 return (1) 136 end 137 138 139 /* 140 ** If @loginame is NULL then we want to set @suid = -1. Otherwise get 141 ** it real value. 142 */ 143 if @loginame is null 144 select @suid = - 1 145 else select @suid = suser_id(@loginame) 146 147 /* 148 ** This transaction also writes a log record for replicating the 149 ** invocation of this procedure. If logexec() fails, the transaction 150 ** is aborted. 151 ** 152 ** IMPORTANT: The name rs_logexec is significant and is used by 153 ** Adaptive Server. 154 */ 155 begin tran rs_logexec 156 157 158 159 /* 160 ** Delete the remote login. 161 */ 162 163 delete master.dbo.sysremotelogins 164 from master.dbo.sysremotelogins l, master.dbo.sysservers s 165 where l.remoteserverid = s.srvid 166 and s.srvname = @remoteserver 167 and l.remoteusername = @remotename 168 and l.suid = @suid 169 170 /* 171 ** Check @@rowcount when it works 172 */ 173 select @rowcount_save = @@rowcount 174 175 if (@rowcount_save > 0) 176 select @rtn_code = 0 177 else 178 select @rtn_code = 1 179 180 181 182 if (@rtn_code = 0) 183 begin 184 /* 185 ** 17512, "Remote login dropped." 186 */ 187 exec sp_getmessage 17512, @msg output 188 print @msg 189 end 190 else 191 begin 192 /* 193 ** 17513, "There is no remote user '%1!' mapped to local user '%2!' from the remote server '%3!'." 194 */ 195 select @rname = isnull(@remotename, "NULL") 196 select @lname = isnull(@loginame, "NULL") 197 raiserror 17513, @rname, @lname, @remoteserver 198 199 goto clean_all 200 end 201 202 /* 203 ** log the command for replication support 204 */ 205 if (@master_is_rep > 0) 206 begin 207 if (logexec(1) != 1) 208 begin 209 /* 210 ** , "17869 Stored procedure %1 failed' 211 ** because '%2' failed in database '%3'." 212 */ 213 raiserror 17869, "sp_dropremotelogin", "logexec()", @dbname 214 goto clean_all 215 end 216 end 217 218 commit tran rs_logexec 219 220 return (0) 221 222 clean_all: 223 rollback tran rs_logexec 224 return (1) 225 226
exec sp_procxmode 'sp_dropremotelogin', 'AnyMode' go Grant Execute on sp_dropremotelogin to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..sysservers (1)  calls proc sybsystemprocs..sp_getmessage  reads table master..sysmessages (1) reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) writes table master..sysremotelogins (1) |
