DatabaseProcApplicationCreatedLinks
sybsystemprocssp_addgroup  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /*	4.2	28.1	05/14/90	sproc/src/addgroup */
4     
5     /*
6     ** Messages for "sp_addgroup"           17240
7     **
8     ** 17240, "'%1!' is not a valid name."
9     ** 17241, "A user with the specified group name already exists."
10    ** 17242, "A group with the specified name already exists."
11    ** 17243, "New group added."  
12    ** 17244, "All group ids have been assigned. No more groups can be added
13    **	   at this time."  
14    ** 17336, "Setting curwrite label to data_low for inserts into sysusers
15    **	   table failed."
16    ** 17756, "The execution of the stored procedure '%1!' in database
17    **         '%2!' was aborted because there was an error in writing the
18    **         replication log record."
19    ** 17265, "A role with the specified name '%1!' already exists in this Server."
20    ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'"
21    */
22    
23    /* 
24    ** IMPORTANT: Please read the following instructions before
25    **   making changes to this stored procedure.
26    **
27    **	To make this stored procedure compatible with High Availability (HA),
28    **	changes to certain system tables must be propagated 
29    **	to the companion server under some conditions.
30    **	The tables include (but are not limited to):
31    **		syslogins, sysservers, sysattributes, systimeranges,
32    **		sysresourcelimits, sysalternates, sysdatabases,
33    **		syslanguages, sysremotelogins, sysloginroles,
34    **		sysalternates (master DB only), systypes (master DB only),
35    **		sysusers (master DB only), sysprotects (master DB only)
36    **	please refer to the HA documentation for detail.
37    **
38    **	Here is what you need to do: 
39    **	For each insert/update/delete statement, add three sections to
40    **	-- start HA transaction prior to the statement
41    **	-- add the statement
42    **	-- add HA synchronization code to propagate the change to the companion
43    **
44    **	For example, if you are adding 
45    **		insert master.dbo.syslogins ......
46    **	the code should look like:
47    **	1. Before that SQL statement:
48    **		
49    **	2. Now, the SQL statement:
50    **		insert master.dbo.syslogins ......
51    **	3. Add a HA synchronization section right after the SQL statement:
52    **		
53    **
54    **	You may need to do similar change for each built-in function you
55    **	want to add.
56    **
57    **	After that, you need to add a separate part at a place where it can not
58    **	be reached by the normal execution path:
59    **	clean_all:
60    **		
61    **		return (1)
62    */
63    
64    create procedure sp_addgroup
65        @grpname varchar(255) /* new group name */
66    as
67    
68        declare @gid int /* group id */
69        declare @msg varchar(1024)
70        declare @dummy int
71        declare @dbname varchar(255)
72        declare @maxlen int
73        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
74        declare @retstat int
75    
76    
77        select @HA_CERTIFIED = 0
78    
79    
80    
81    
82        /* check to see if we are using HA specific SP for a HA enabled server */
83        exec @retstat = sp_ha_check_certified 'sp_addgroup', @HA_CERTIFIED
84        if (@retstat != 0)
85            return (1)
86    
87        set transaction isolation level 1
88        if @@trancount = 0
89        begin
90            set chained off
91        end
92    
93        /*
94        **  Only the Database Owner (DBO) or
95        **  Accounts with SA or SSO role can execute it.
96        **  Call proc_role() with the required SA role.
97        */
98        if (user_id() != 1)
99        begin
100           if (charindex("sa_role", show_role()) = 0 and
101                   charindex("sso_role", show_role()) = 0)
102           begin
103               select @dummy = proc_role("sa_role")
104               select @dummy = proc_role("sso_role")
105               return (1)
106           end
107       end
108   
109       if (charindex("sa_role", show_role()) > 0)
110           select @dummy = proc_role("sa_role")
111   
112       if (charindex("sso_role", show_role()) > 0)
113           select @dummy = proc_role("sso_role")
114   
115       /*
116       **  Check to see that the @grpname is valid.
117       */
118       if (@grpname is not null)
119       begin
120           select @maxlen = length from syscolumns
121           where id = object_id("sysusers") and name = "name"
122   
123           if valid_name(@grpname, @maxlen) = 0
124           begin
125               /*
126               ** 17240, "'%1!' is not a valid name."
127               */
128               raiserror 17240, @grpname
129               return 1
130           end
131       end
132   
133       /*
134       **  Check to see that @grpname is not a role name.
135       */
136       if exists (select srid from master.dbo.syssrvroles
137               where name = @grpname)
138       begin
139           /*
140           ** 17265, "A role with the specified name '%1!' already exists in this
141           **	   Server."
142           */
143           raiserror 17265, @grpname
144           return (1)
145       end
146   
147       /*
148       **  See if the new group name is already being used as a user or group name.
149       */
150       select @gid = uid
151       from sysusers
152       where name = @grpname
153   
154       /*
155       **  public group has id = 0
156       */
157       if @gid is not null
158       begin
159   
160           if (@gid != 0) and (@gid < @@mingroupid or @gid > @@maxgroupid)
161           begin
162               /*
163               ** 17241, "A user with the specified group name already exists."
164               */
165               raiserror 17241
166           end
167           else
168           begin
169               /*
170               ** 17242, "A group with the specified name already exists."
171               */
172               raiserror 17242
173           end
174           return (1)
175       end
176   
177       /*
178       **  Now get the group id for the new group.  It is the current maximum group
179       **  number + 1.  If this is the first group use the lowest possible group id
180       **  @@mingroupid.
181       */
182       select @gid = max(uid) + 1
183       from sysusers where uid = gid and gid <= @@maxgroupid
184   
185       /*
186       ** No more group ids available
187       */
188       if @gid = @@maxgroupid + 1
189       begin
190           /*
191           ** 17244, "All group ids have been assigned. No more groups can be 
192           ** 	added at this time."  
193           */
194           raiserror 17244
195           return (1)
196       end
197   
198       /*
199       **  This is the first group.
200       */
201       if @gid < @@mingroupid or @gid is NULL
202           select @gid = @@mingroupid
203   
204   
205   
206   
207       /*
208       **  Create the group.
209       */
210   
211       /* 
212       ** This transaction also writes a log record for replicating the
213       ** invocation of this procedure. If logexec() fails, the transaction
214       ** is aborted.
215       **
216       ** IMPORTANT: The name rs_logexec is significant and is used by
217       ** Replication Server.
218       */
219       begin transaction rs_logexec
220   
221   
222   
223       insert into sysusers(uid, suid, gid, name, environ)
224       values (@gid, - 2, @gid, @grpname, "")
225   
226   
227       /*
228       ** Write the log record to replicate this invocation 
229       ** of the stored procedure.
230       */
231       if (logexec() != 1)
232       begin
233           /*
234           ** 17756, "The execution of the stored procedure '%1!'
235           ** 	   in database '%2!' was aborted because there
236           ** 	   was an error in writing the replication log
237           **	   record."
238           */
239           select @dbname = db_name()
240           raiserror 17756, "sp_addgroup", @dbname
241   
242           rollback transaction rs_logexec
243           return (1)
244       end
245   
246       commit transaction rs_logexec
247   
248       /*
249       ** 17243, "New group added."  
250       */
251       exec sp_getmessage 17243, @msg out
252       print @msg
253   
254       return (0)
255   
256   clean_all:
257       rollback transaction rs_logexec
258       return (1)
259   
260   


exec sp_procxmode 'sp_addgroup', 'AnyMode'
go

Grant Execute on sp_addgroup to public
go
DEFECTS
 MURC 6 Unreachable Code 256
 MURC 6 Unreachable Code 257
 MURC 6 Unreachable Code 258
 MEST 4 Empty String will be replaced by Single Space 224
 MTYP 4 Assignment type mismatch name: sysname = varchar(255) 224
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_addgroup  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check @@error after insert 223
 MNER 3 No Error Check should check return value of exec 251
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 85
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 112
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 144
 MUCO 3 Useless Code Useless Brackets 174
 MUCO 3 Useless Code Useless Brackets 195
 MUCO 3 Useless Code Useless Brackets 231
 MUCO 3 Useless Code Useless Brackets 243
 MUCO 3 Useless Code Useless Brackets 254
 MUCO 3 Useless Code Useless Brackets 258
 QAFM 3 Var Assignment from potentially many rows 120
 QISO 3 Set isolation level 87
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
121
 VNRD 3 Variable is not read @dummy 113
 MSUB 2 Subquery Marker 136
 MTR1 2 Metrics: Comments Ratio Comments: 62% 64
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 15 = 20dec - 7exi + 2 64
 MTR3 2 Metrics: Query Complexity Complexity: 87 64

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..syscolumns  
reads table master..syssrvroles (1)  
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)  
read_writes table sybsystemprocs..sysusers