DatabaseProcApplicationCreatedLinks
sybsystemprocssp_adduser  31 Aug 14Defects 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
DEFECTS
 MURC 6 Unreachable Code 426
 MURC 6 Unreachable Code 427
 MURC 6 Unreachable Code 428
 MCTR 4 Conditional Begin Tran or Commit Tran 182
 MCTR 4 Conditional Begin Tran or Commit Tran 210
 MTYP 4 Assignment type mismatch name: sysname = varchar(255) 391
 MGTP 3 Grant to public master..syslogins  
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_adduser  
 MGTP 3 Grant to public sybsystemprocs..sysalternates  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check @@error after insert 186
 MNER 3 No Error Check should check @@error after insert 390
 MNER 3 No Error Check should check return value of exec 421
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 93
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 195
 MUCO 3 Useless Code Useless Brackets 207
 MUCO 3 Useless Code Useless Brackets 215
 MUCO 3 Useless Code Useless Brackets 233
 MUCO 3 Useless Code Useless Brackets 249
 MUCO 3 Useless Code Useless Brackets 264
 MUCO 3 Useless Code Useless Brackets 279
 MUCO 3 Useless Code Useless Brackets 294
 MUCO 3 Useless Code Useless Brackets 318
 MUCO 3 Useless Code Useless Brackets 369
 MUCO 3 Useless Code Useless Brackets 398
 MUCO 3 Useless Code Useless Brackets 410
 MUCO 3 Useless Code Useless Brackets 424
 MUCO 3 Useless Code Useless Brackets 428
 QAFM 3 Var Assignment from potentially many rows 134
 QFID 3 Force index master..syslogins 153
 QFID 3 Force index sybsystemprocs..sysusers 157
 QFID 3 Force index master..syslogins 223
 QFID 3 Force index sybsystemprocs..sysusers 242
 QFID 3 Force index sybsystemprocs..sysusers 257
 QISO 3 Set isolation level 100
 QIWC 3 Insert with not all columns specified missing 1 columns out of 5 186
 QIWC 3 Insert with not all columns specified missing 1 columns out of 5 390
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
135
 QSWV 3 Sarg with variable @suid, Candidate Index: sysusers.csysusers clustered(suid) F 258
 QSWV 3 Sarg with variable @@guestuserid, Candidate Index: sysusers.ncsysusers2 unique(uid) S 337
 QSWV 3 Sarg with variable @@mingroupid, Candidate Index: sysusers.ncsysusers2 unique(uid) S 337
 QSWV 3 Sarg with variable @@maxgroupid, Candidate Index: sysusers.ncsysusers2 unique(uid) S 338
 QSWV 3 Sarg with variable @@maxuserid, Candidate Index: sysusers.ncsysusers2 unique(uid) S 338
 QSWV 3 Sarg with variable @@invaliduserid, Candidate Index: sysusers.ncsysusers2 unique(uid) S 356
 QSWV 3 Sarg with variable @@minuserid, Candidate Index: sysusers.ncsysusers2 unique(uid) S 356
 QTLO 3 Top-Level OR 337
 VNRD 3 Variable is not read @dummy 121
 MSUB 2 Subquery Marker 152
 MSUB 2 Subquery Marker 156
 MSUB 2 Subquery Marker 241
 MSUB 2 Subquery Marker 256
 MSUB 2 Subquery Marker 271
 MSUB 2 Subquery Marker 285
 MSUB 2 Subquery Marker 308
 MTR1 2 Metrics: Comments Ratio Comments: 61% 69
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 25 = 37dec - 14exi + 2 69
 MTR3 2 Metrics: Query Complexity Complexity: 161 69

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)