| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_addalias | | 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/addalias */ 4 5 /* 6 ** Messages for "sp_addalias" 17230 7 ** 8 ** 17231, "No login with the specified name exists." 9 ** 17232, "No user with the specified name exists in the current database." 10 ** 17233, "'%1!' is already a user in the current database." 11 ** 17234, "The specified user name is already aliased." 12 ** 17235, "Alias user added." 13 ** 17236, "Setting curwrite label to data_low for inserts into sysalternates 14 ** table failed." 15 ** 17240, "'%1!' is not a valid name." 16 ** 17289, "Set your curwrite to the hurdle of current database." 17 ** 17756, "The execution of the stored procedure '%1!' in database 18 ** '%2!' was aborted because there was an error in writing the 19 ** replication log record." 20 ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'" 21 ** 18778, "A login with Login name '%1!' AND login id '%2!' could not be found in syslogins." 22 ** 18795, "Unable to find a user with name '%1!' and login id '%2!' in sysusers." 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. Before that SQL statement: 50 ** 51 ** 2. Now, the SQL statement: 52 ** insert master.dbo.syslogins ...... 53 ** 3. Add a HA synchronization section right after the SQL statement: 54 ** 55 ** 56 ** You may need to do similar change for each built-in function you 57 ** want to add. 58 ** 59 ** After that, you need to add a separate part at a place where it can not 60 ** be reached by the normal execution path: 61 ** clean_all: 62 ** 63 ** return (1) 64 */ 65 66 create procedure sp_addalias 67 @loginame varchar(255), /* the name of the pretender */ 68 @name_in_db varchar(255) /* who the pretender wants to pretend to be */ 69 as 70 71 declare @suid int /* the suid of the pretender */ 72 declare @asuid int /* the suid of the person to impersonate */ 73 declare @msg varchar(1024) 74 declare @name varchar(255) 75 declare @dbname varchar(255) 76 declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */ 77 declare @retstat int 78 declare @dummy int 79 80 select @HA_CERTIFIED = 0 81 82 83 84 /* check to see if we are using HA specific SP for a HA enabled server */ 85 exec @retstat = sp_ha_check_certified 'sp_addalias', @HA_CERTIFIED 86 if (@retstat != 0) 87 return (1) 88 89 set transaction isolation level 1 90 if @@trancount = 0 91 begin 92 set chained off 93 end 94 95 /* 96 ** Only the Database Owner (DBO) or 97 ** Accounts with SA or SSO role can execute it. 98 ** If the user has SA role, it's uid will 99 ** be DBO uid (1). 100 */ 101 if ((user_id() != 1) and (charindex("sso_role", show_role()) = 0)) 102 begin 103 /* 104 ** proc_role() will raise permission errors 105 ** and send audit records to the audit trail 106 */ 107 select @dummy = proc_role("sa_role") 108 select @dummy = proc_role("sso_role") 109 return (1) 110 end 111 112 /* Send apropriate audit records. */ 113 if (charindex("sa_role", show_role()) > 0) 114 select @dummy = proc_role("sa_role") 115 if (charindex("sso_role", show_role()) > 0) 116 select @dummy = proc_role("sso_role") 117 118 /* 119 ** Make sure that the pretender has an account. 120 */ 121 select @suid = suid 122 from master.dbo.syslogins 123 where name = @loginame 124 and ((status & 512) != 512) /* not LOGIN PROFILE */ 125 126 if @suid is NULL 127 begin 128 /* 17231, "No login with the specified name exists." */ 129 130 raiserror 17231 131 return (1) 132 end 133 134 /* 135 ** Get the suid of the person we want to pretend to be. 136 */ 137 select @asuid = suid 138 from sysusers 139 where name = @name_in_db 140 and ((uid >= @@minuserid and uid < @@mingroupid and uid != 0) 141 or uid > @@maxgroupid) 142 143 /* 144 ** Does the user to be impersonated exist in the current database? 145 */ 146 if @asuid is NULL 147 begin 148 /* 149 ** 17232, "No user with the specified name exists in the 150 ** current database." 151 */ 152 153 raiserror 17232 154 return (1) 155 end 156 157 /* 158 ** Does the login to do the impersonating already a user in the current db? 159 */ 160 if exists (select * 161 from sysusers 162 where suid = @suid) 163 begin 164 /* 17233, "'%1!' is already a user in the current database." */ 165 select @name = name 166 from sysusers 167 where suid = @suid 168 169 raiserror 17233, @name 170 return (1) 171 end 172 173 /* 174 ** Is the person already aliased to a user? 175 */ 176 if exists (select * 177 from sysalternates 178 where suid = @suid) 179 begin 180 /* 17234, "The specified user name is already aliased." */ 181 182 raiserror 17234 183 return (1) 184 end 185 186 187 188 /* 189 ** Add the alias. 190 */ 191 192 /* 193 ** This transaction also writes a log record for replicating the 194 ** invocation of this procedure. If logexec() fails, the transaction 195 ** is aborted. 196 ** 197 ** IMPORTANT: The name rs_logexec is significant and is used by 198 ** Replication Server. 199 */ 200 begin transaction rs_logexec 201 202 203 204 insert into sysalternates(suid, altsuid) 205 values (@suid, @asuid) 206 207 /* 208 ** Write the log record to replicate this invocation 209 ** of the stored procedure. 210 */ 211 if (logexec() != 1) 212 begin 213 /* 214 ** 17756, "The execution of the stored procedure '%1!' 215 ** in database '%2!' was aborted because there 216 ** was an error in writing the replication log 217 ** record." 218 */ 219 select @dbname = db_name() 220 raiserror 17756, "sp_addalias", @dbname 221 222 rollback transaction rs_logexec 223 return (1) 224 end 225 226 227 228 commit transaction rs_logexec 229 230 /* Update protection timestamp in Resource */ 231 grant all to null 232 233 /* 17235, "Alias user added." */ 234 exec sp_getmessage 17235, @msg out 235 print @msg 236 237 return (0) 238 239 clean_all: 240 rollback transaction rs_logexec 241 return (1) 242 243
exec sp_procxmode 'sp_addalias', 'AnyMode' go Grant Execute on sp_addalias to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..sysusers calls proc sybsystemprocs..sp_ha_check_certified  reads table tempdb..sysobjects (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) reads table master..syslogins (1) read_writes table sybsystemprocs..sysalternates |
