| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_adduser | | 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/adduser */ 4 5 /* 6 ** Messages for "sp_adduser" 17330 7 ** 8 ** 17240, "'" + @name_in_db + "' is not a valid name." 9 ** 17231, "No login with the specified name exists." 10 ** 17330, "A user with the same name already exists in the database." 11 ** 17331, "User already has a login under a different name." 12 ** 17332, "User already has alias access to the database." 13 ** 17333, "No group with the specified name exists." 14 ** 17334, "All user ids have been assigned." 15 ** 17335, "New user added." 16 ** 17336, "Setting curwrite label to data_low for inserts into sysusers 17 ** table failed." 18 ** 17289, "Set your curwrite to the hurdle of current database." 19 ** 17756, "The execution of the stored procedure '%1!' in database 20 ** '%2!' was aborted because there was an error in writing the 21 ** replication log record." 22 ** 17265, "A role with the specified name '%1!' already exists in this Server." 23 ** 18773, "HA_LOG: HA consistency check failure in stored procedure '% 1!' on the companion server '%2!'" 24 ** 18778, "A login with Login name '%1!' AND login id '%2!' could not be found in syslogins." 25 ** 18796, "A user with name '%1!' or login id '%2!' already exists in sysusers." 26 */ 27 28 /* 29 ** IMPORTANT: Please read the following instructions before 30 ** making changes to this stored procedure. 31 ** 32 ** To make this stored procedure compatible with High Availability (HA), 33 ** changes to certain system tables must be propagated 34 ** to the companion server under some conditions. 35 ** The tables include (but are not limited to): 36 ** syslogins, sysservers, sysattributes, systimeranges, 37 ** sysresourcelimits, sysalternates, sysdatabases, 38 ** syslanguages, sysremotelogins, sysloginroles, 39 ** sysalternates (master DB only), systypes (master DB only), 40 ** sysusers (master DB only), sysprotects (master DB only) 41 ** please refer to the HA documentation for detail. 42 ** 43 ** Here is what you need to do: 44 ** For each insert/update/delete statement, add three sections to 45 ** -- start HA transaction prior to the statement 46 ** -- add the statement 47 ** -- add HA synchronization code to propagate the change to the companion 48 ** 49 ** For example, if you are adding 50 ** insert master.dbo.syslogins ...... 51 ** the code should look like: 52 ** 1. Before that SQL statement: 53 ** 54 ** 2. Now, the SQL statement: 55 ** insert master.dbo.syslogins ...... 56 ** 3. Add a HA synchronization section right after the SQL statement: 57 ** 58 ** 59 ** You may need to do similar change for each built-in function you 60 ** want to add. 61 ** 62 ** After that, you need to add a separate part at a place where it can not 63 ** be reached by the normal execution path: 64 ** clean_all: 65 ** 66 ** return (1) 67 */ 68 69 create procedure sp_adduser 70 @loginame varchar(255), /* user's login name in syslogins */ 71 @name_in_db varchar(255) = NULL, /* user's name to add to current db */ 72 @grpname varchar(255) = NULL /* group to put new user in */ 73 as 74 75 declare @suid int /* user's system id */ 76 declare @grpid int /* group id of group to put user in */ 77 declare @uid int /* new user's id */ 78 declare @msg varchar(1024) 79 declare @dummy int 80 declare @dbname varchar(255) 81 declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */ 82 declare @retstat int 83 declare @maxlen int 84 85 select @uid = NULL 86 select @HA_CERTIFIED = 0 87 88 89 90 /* check to see if we are using HA specific SP for a HA enabled server */ 91 exec @retstat = sp_ha_check_certified 'sp_adduser', @HA_CERTIFIED 92 if (@retstat != 0) 93 return (1) 94 95 if @@trancount = 0 96 begin 97 set chained off 98 end 99 100 set transaction isolation level 1 101 102 /* 103 ** Only the Database Owner (DBO) or 104 ** Accounts with SA or SSO role can execute it. 105 ** Call proc_role() with the required SA role. 106 */ 107 if (user_id() != 1) 108 begin 109 if (charindex("sa_role", show_role()) = 0 and 110 charindex("sso_role", show_role()) = 0) 111 begin 112 select @dummy = proc_role("sa_role") 113 select @dummy = proc_role("sso_role") 114 return (1) 115 end 116 end 117 118 if (charindex("sa_role", show_role()) > 0) 119 select @dummy = proc_role("sa_role") 120 if (charindex("sso_role", show_role()) > 0) 121 select @dummy = proc_role("sso_role") 122 123 /* 124 ** If no new user name is given, use the login name. 125 */ 126 if @name_in_db is NULL 127 select @name_in_db = @loginame 128 129 /* 130 ** Check to see that the @name_in_db is valid. 131 */ 132 if (@name_in_db is not null) 133 begin 134 select @maxlen = length from syscolumns 135 where id = object_id("sysusers") and name = "name" 136 137 if valid_name(@name_in_db, @maxlen) = 0 138 begin 139 /* 140 ** 17240, "'" + @name_in_db + "' is not a valid name." 141 */ 142 raiserror 17240, @name_in_db 143 return (1) 144 end 145 end 146 147 /* 148 ** The name guest is a special case. If it doesn't have a login it 149 ** can still be a valid user. We'll catch it here and special case it. 150 */ 151 if @loginame = "guest" and not exists 152 (select name 153 from master.dbo.syslogins(index ncsyslogins) 154 where name = @loginame) 155 begin 156 if exists (select * 157 from sysusers(index ncsysusers1) 158 where name = @loginame) 159 begin 160 /* 161 ** 17330, "A user with the same name already exists in the database." 162 */ 163 raiserror 17330 164 return (1) 165 end 166 167 168 169 /* 170 ** Add the guest user and return. 171 */ 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 ** Replication Server. 180 */ 181 182 begin transaction rs_logexec 183 184 185 186 insert into sysusers(uid, suid, gid, name) 187 values (@@guestuserid, - 1, 0, "guest") 188 189 190 191 /* 192 ** Write the log record to replicate this invocation 193 ** of the stored procedure. 194 */ 195 if (logexec() != 1) 196 begin 197 /* 198 ** 17756, "The execution of the stored procedure 199 ** '%1!' in database '%2!' was aborted 200 ** because there was an error in writing 201 ** the replication log record." 202 */ 203 select @dbname = db_name() 204 raiserror 17756, "sp_adduser", @dbname 205 206 rollback transaction rs_logexec 207 return (1) 208 end 209 210 commit transaction rs_logexec 211 212 /* Update protection timestamp in Resource */ 213 grant all to null 214 215 return (0) 216 end 217 218 /* 219 ** Check to see that the user has a login name. 220 ** We'll also initialize @grpid to 0 while we're here. 221 */ 222 select @suid = suid, @grpid = 0 223 from master.dbo.syslogins(index ncsyslogins) 224 where name = @loginame and 225 ((status & 512) != 512) /* not LOGIN PROFILE */ 226 227 if @suid is NULL 228 begin 229 /* 230 ** 17231, "No login with the specified name exists." 231 */ 232 raiserror 17231 233 return (1) 234 end 235 236 /* 237 ** Now check to see if the user already exists in the database. 238 ** This will also check if there is a role or group with @name_in_db 239 ** already in this database. 240 */ 241 if exists (select * 242 from sysusers(index ncsysusers1) 243 where name = @name_in_db) 244 begin 245 /* 246 ** 17330, "A user with the same name already exists in the database." 247 */ 248 raiserror 17330 249 return (1) 250 end 251 252 /* 253 ** See if the user already has an account under a different name. 254 ** That is, is the user's suid already in the sysusers table. 255 */ 256 if exists (select * 257 from sysusers(index sysusers) 258 where suid = @suid) 259 begin 260 /* 261 ** 17331, "User already has a login under a different name." 262 */ 263 raiserror 17331 264 return (1) 265 end 266 267 /* 268 ** See if the user is known in the database already with an alias. 269 ** That is, does the user's suid appear in the sysalternates table. 270 */ 271 if exists (select * 272 from sysalternates 273 where suid = @suid) 274 begin 275 /* 276 ** 17332, "User already has alias access to the database." 277 */ 278 raiserror 17332 279 return (1) 280 end 281 282 /* 283 ** Make sure a role does not already exist with name. 284 */ 285 if exists (select * 286 from master.dbo.syssrvroles 287 where name = @name_in_db) 288 begin 289 /* 290 ** 17265, "A role with the specified name '%1!' already exists in this 291 ** Server." 292 */ 293 raiserror 17265, @name_in_db 294 return (1) 295 end 296 297 /* 298 ** If a group name is given, check to see that it is valid. 299 ** public group has id = 0 300 */ 301 if @grpname is not NULL 302 begin 303 select @grpid = - 1 304 select @grpid = gid from sysusers 305 where name = @grpname 306 and ((uid = 0) or 307 (uid between @@mingroupid and @@maxgroupid)) 308 and not exists (select name from master.dbo.syssrvroles 309 where name = @grpname) 310 311 312 if @grpid = - 1 313 begin 314 /* 315 ** 17333, "No group with the specified name exists." 316 */ 317 raiserror 17333 318 return (1) 319 end 320 end 321 322 /* 323 ** Add the user to the sysusers table. 324 ** Check to see if the special user 'guest' (uid = 2) has already 325 ** been added. If not, then the uid to use is 3, otherwise max(uid) + 1. 326 ** uid can go upto (@@mingroupid - 1) and then there will be a discontinuity 327 ** in the range @@mingroupid to @@maxgroupid which are reserved for groups. 328 ** We can continue assigning uids from (@@maxgroupid + 1) upto @@maxuserid. 329 ** After reaching @@maxuserid, we can wrap around to the -ve space and 330 ** assign uid in the range (@@invaliduserid - 1) to @@minuserid. 331 */ 332 /* 333 ** HA NOTE: Whenever the logic of uid generation is changed, please also 334 ** change it same way to the @uid_hacmp 335 */ 336 select @uid = max(uid) from sysusers 337 where (uid > @@guestuserid and uid < @@mingroupid) 338 or (uid > @@maxgroupid and uid <= @@maxuserid) 339 340 if @uid is NULL 341 /* The first regular user has uid = 3 */ 342 select @uid = @@guestuserid + 1 343 else 344 begin 345 if @uid = @@mingroupid - 1 346 /* If we step into group range, skip group values */ 347 select @uid = @@maxgroupid + 1 348 else 349 begin 350 if @uid <> @@maxuserid 351 select @uid = @uid + 1 352 else 353 begin 354 /* @@maxuserid has been used up, check the -ve space */ 355 select @uid = min(uid) from sysusers 356 where uid < @@invaliduserid AND uid >= @@minuserid 357 358 if @uid is NULL 359 /* the first negative uid is -2 */ 360 select @uid = @@invaliduserid - 1 361 else 362 begin 363 if @uid <> @@minuserid 364 select @uid = @uid - 1 365 else 366 begin 367 /* 17334, All uids have been assigned */ 368 raiserror 17334 369 return (1) 370 end 371 end 372 end 373 end 374 end 375 376 377 378 /* 379 ** This transaction also writes a log record for replicating the 380 ** invocation of this procedure. If logexec() fails, the transaction 381 ** is aborted. 382 ** 383 ** IMPORTANT: The name rs_logexec is significant and is used by 384 ** Replication Server. 385 */ 386 begin transaction rs_logexec 387 388 389 390 insert into sysusers(uid, suid, gid, name) 391 values (@uid, @suid, @grpid, @name_in_db) 392 393 394 /* 395 ** Write the log record to replicate this invocation 396 ** of the stored procedure. 397 */ 398 if (logexec() != 1) 399 begin 400 /* 401 ** 17756, "The execution of the stored procedure '%1!' 402 ** in database '%2!' was aborted because there 403 ** was an error in writing the replication log 404 ** record." 405 */ 406 select @dbname = db_name() 407 raiserror 17756, "sp_adduser", @dbname 408 409 rollback transaction rs_logexec 410 return (1) 411 end 412 413 commit transaction rs_logexec 414 415 /* Update protection timestamp in Resource */ 416 grant all to null 417 418 /* 419 ** 17335, "New user added." 420 */ 421 exec sp_getmessage 17335, @msg output 422 print @msg 423 424 return (0) 425 426 clean_all: 427 rollback transaction rs_logexec 428 return (1) 429
exec sp_procxmode 'sp_adduser', 'AnyMode' go Grant Execute on sp_adduser to public go
| DEPENDENCIES |
| PROCS AND TABLES USED read_writes table sybsystemprocs..sysusers reads table sybsystemprocs..sysalternates calls proc sybsystemprocs..sp_getmessage  calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1)  reads table sybsystemprocs..sysusermessages reads table master..syslanguages (1) reads table master..sysmessages (1) reads table master..syssrvroles (1) reads table sybsystemprocs..syscolumns calls proc sybsystemprocs..sp_ha_check_certified  reads table tempdb..sysobjects (1)  reads table master..syslogins (1) |
