| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_dropexternlogin | | 31 Aug 14 | Defects 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 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 82 select @srid = - 1, @retstat = 0, @HA_CERTIFIED = 0 83 84 85 86 /* 87 ** Check to see if we are using HA specific SP for a HA enabled 88 ** server. 89 */ 90 exec @retstat = sp_ha_check_certified 'sp_dropexternlogin', 91 @HA_CERTIFIED 92 if (@retstat != 0) 93 return (1) 94 95 /* 96 ** If we're in a transaction, disallow this since it might make 97 ** recovery impossible. 98 */ 99 if (@@trancount > 0) 100 begin 101 /* 102 ** 17260 Can't run '%1!' from within a transaction. 103 */ 104 raiserror 17260, "sp_dropexternlogin" 105 goto raise_error 106 end 107 108 set chained off 109 110 set transaction isolation level 1 111 112 /* 113 ** Validate the server name. 114 */ 115 select @srvid = srvid, @srvclass = srvclass 116 from master.dbo.sysservers 117 where srvname = @server 118 119 if (@@rowcount = 0) 120 begin 121 /* 122 ** 18307, "Server name '%1!' does not exist in sysservers." 123 */ 124 raiserror 18307, @server 125 goto raise_error 126 end 127 128 /* 129 ** If @rolename was specified, ignore @loginame. 130 */ 131 if (@rolename is not null) 132 begin 133 select @loginame = null 134 135 /* 136 ** Check that the @rolename is valid. 137 */ 138 select @srid = srid from master.dbo.syssrvroles 139 where name = @rolename 140 141 if (@@rowcount = 0) 142 begin 143 /* 144 ** 18342, "Invalid name '%1!'. This role or user does 145 ** not exist in this SQL Server." 146 */ 147 raiserror 18342, @rolename 148 goto raise_error 149 end 150 end 151 152 if (@loginame is null) 153 begin 154 select @suid = - 1 155 end 156 else 157 begin 158 select @suid = suser_id(@loginame) 159 160 /* 161 ** Check that the @loginame is valid. 162 */ 163 if (@suid is null) 164 begin 165 /* 166 ** 18294 "User '%1!' is not a local user -- request 167 ** denied." 168 */ 169 raiserror 18294, @loginame 170 goto raise_error 171 end 172 end 173 174 /* 175 ** Only a user with sa_role or sso_role can add extern logins for 176 ** another user or for a role. 177 */ 178 if (@suid != suser_id()) 179 begin 180 /* 181 ** check if user has sa or sso role, proc_role will 182 ** also do auditing if required. proc_role will also 183 ** print error message if required. 184 */ 185 if (charindex("sa_role", show_role()) = 0 and 186 charindex("sso_role", show_role()) = 0) 187 begin 188 select @dummy = proc_role("sa_role") 189 select @dummy = proc_role("sso_role") 190 goto raise_error 191 end 192 end 193 194 195 196 begin tran rs_logexec 197 198 199 200 /* 201 ** Delete the remote login. 202 */ 203 if ((@srvclass != 12) and (@srvclass != 14) and (@srvclass != 15)) 204 select @dso_class = 9 205 else 206 select @dso_class = 21 207 208 select @attrib = 0 209 210 delete master.dbo.sysattributes 211 where class = @dso_class and attribute = @attrib and 212 object_info1 = @srvid and object = @suid and 213 (object_info2 = @srid or object_info2 is null) 214 /* 215 ** Check @@rowcount when it works 216 */ 217 if (@@rowcount > 0) 218 begin 219 select @action = 3 /* attribute drop */ 220 221 222 if (logexec(1) != 1) 223 begin 224 /* 225 ** 17756, "The execution of the stored procedure '%1!' 226 ** in database '%2!' was aborted because there 227 ** was an error in writing the replication log 228 ** record." 229 */ 230 select @dbname = db_name() 231 raiserror 17756, "sp_dropexternlogin", @dbname 232 233 select @retstat = 1 234 goto clean_all 235 end 236 237 commit tran rs_logexec 238 239 if (@@error != 0) 240 goto raise_error 241 242 /* 243 ** 18321, "Remote login/alias dropped" 244 */ 245 exec sp_getmessage 18321, @msg output 246 print @msg 247 248 /* 249 ** Sync the in-memory RDES with the new values 250 ** in sysattributes. 251 */ 252 if (attrib_notify(@dso_class, @attrib, "EL", @suid, @srvid, 253 @srid, null, null, null, null, null, null, null, 254 @action) = 0) 255 begin 256 select @retstat = 1 257 end 258 259 260 261 end 262 else 263 begin 264 /* 265 ** 18319, "There is no external login for server '%1!'." 266 */ 267 exec sp_getmessage 18319, @msg output 268 print @msg, @server 269 goto clean_all 270 end 271 272 return (@retstat) 273 274 clean_all: 275 rollback tran rs_logexec 276 277 raise_error: 278 279 280 return (1) 281 end 282
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_getmessage  reads table sybsystemprocs..sysusermessages reads table master..sysmessages (1) reads table master..syslanguages (1) calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) calls proc sybsystemprocs..sp_ha_check_certified  reads table tempdb..sysobjects (1)  writes table master..sysattributes (1) reads table master..syssrvroles (1) |
