Database | Proc | Application | Created | Links |
sybsystemprocs | sp_addgroup | 31 Aug 14 | Defects 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
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 |