| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_dropgroup | | 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/dropgroup */ 4 5 /* 6 ** Messages for "sp_dropgroup" 17486 7 ** 8 ** 17289, "Set your curwrite to the hurdle of current database." 9 ** 17333, "No group with the specified name exists." 10 ** 17486, "Can't drop the group 'public'." 11 ** 17487, "You cannot drop group because it owns objects in database." 12 ** 17488, "Group has members. It must be empty before it can be dropped." 13 ** 17489, "Group has been dropped." 14 ** 17756, "The execution of the stored procedure '%1!' in database 15 ** '%2!' was aborted because there was an error in writing the 16 ** replication log record." 17 ** 18075, "Set your maxwrite label correctly." 18 ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'" 19 ** 20 */ 21 22 /* 23 ** IMPORTANT: Please read the following instructions before 24 ** making changes to this stored procedure. 25 ** 26 ** To make this stored procedure compatible with High Availability (HA), 27 ** changes to certain system tables must be propagated 28 ** to the companion server under some conditions. 29 ** The tables include (but are not limited to): 30 ** syslogins, sysservers, sysattributes, systimeranges, 31 ** sysresourcelimits, sysalternates, sysdatabases, 32 ** syslanguages, sysremotelogins, sysloginroles, 33 ** sysalternates (master DB only), systypes (master DB only), 34 ** sysusers (master DB only), sysprotects (master DB only) 35 ** please refer to the HA documentation for detail. 36 ** 37 ** Here is what you need to do: 38 ** For each insert/update/delete statement, add three sections to 39 ** -- start HA transaction prior to the statement 40 ** -- add the statement 41 ** -- add HA synchronization code to propagate the change to the companion 42 ** 43 ** For example, if you are adding 44 ** insert master.dbo.syslogins ...... 45 ** the code should look like: 46 ** 1. Before that SQL statement: 47 ** 48 ** 2. Now, the SQL statement: 49 ** insert master.dbo.syslogins ...... 50 ** 3. Add a HA synchronization section right after the SQL statement: 51 ** 52 ** 53 ** You may need to do similar change for each built-in function you 54 ** want to add. 55 ** 56 ** After that, you need to add a separate part at a place where it can not 57 ** be reached by the normal execution path: 58 ** clean_all: 59 ** 60 ** return (1) 61 ** 62 */ 63 64 create procedure sp_dropgroup 65 @grpname varchar(255) /* group to be dropped */ 66 as 67 68 declare @gid int /* group id of the group to be dropped */ 69 declare @msg varchar(1024) 70 declare @dummy int 71 declare @dbname varchar(255) 72 declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */ 73 declare @retstat int 74 75 76 select @HA_CERTIFIED = 0 77 78 79 80 81 /* check to see if we are using HA specific SP for a HA enabled server */ 82 exec @retstat = sp_ha_check_certified 'sp_dropgroup', @HA_CERTIFIED 83 if (@retstat != 0) 84 return (1) 85 86 if @@trancount = 0 87 begin 88 set chained off 89 end 90 91 set transaction isolation level 1 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 ** See if the group exists. 117 */ 118 select @gid = uid from sysusers 119 where name = @grpname 120 and ((uid between @@mingroupid and @@maxgroupid) or uid = 0) 121 and not exists (select name from master.dbo.syssrvroles where name = @grpname) 122 123 if @gid is NULL 124 begin 125 /* 126 ** 17333, "No group with the specified name exists." 127 */ 128 raiserror 17333 129 return (1) 130 end 131 132 /* 133 ** Can't drop the group public. 134 */ 135 if @gid = 0 136 begin 137 /* 138 ** 17486, "Can't drop the group 'public'." 139 */ 140 raiserror 17486 141 return (1) 142 end 143 144 /* 145 ** Check to see if the group owns anything. If so, return. 146 */ 147 if exists (select * from sysobjects where uid = @gid) 148 begin 149 /* 150 ** 17487, "You cannot drop group because it owns objects in database." 151 */ 152 raiserror 17487 153 154 /* 155 ** Show what is owned by the group. 156 */ 157 select s.name, s.type, owner = u.name 158 from sysobjects s, sysusers u 159 where s.id = @gid 160 and u.uid = @gid 161 return (1) 162 end 163 164 165 /* 166 ** Check to see that nobody is in the group. If so, return. 167 */ 168 if (select count(*) from sysusers 169 where gid = @gid and (uid < @@mingroupid or uid > @@maxgroupid)) != 0 170 begin 171 /* 172 ** 17488, "Group has members. It must be empty before it can be dropped." 173 */ 174 raiserror 17488 175 176 /* 177 ** Show who is in the group. 178 */ 179 select name from sysusers 180 where gid = @gid and (uid < @@mingroupid or uid > @@maxgroupid) 181 182 return (1) 183 end 184 185 186 187 out_of_HA_checking: 188 189 /* 190 ** Drop the group. 191 ** Also drop any references to the group in the sysprotects table. 192 */ 193 begin transaction rs_logexec 194 195 196 197 delete from sysusers 198 where uid = @gid 199 200 201 delete from sysprotects 202 where uid = @gid 203 204 205 206 /* 207 ** Write the log record to replicate this invocation 208 ** of the stored procedure. 209 */ 210 if (logexec() != 1) 211 begin 212 /* 213 ** 17756, "The execution of the stored procedure '%1!' in 214 ** database '%2!' was aborted because there was an 215 ** error in writing the replication log record." 216 */ 217 select @dbname = db_name() 218 raiserror 17756, "sp_dropgroup", @dbname 219 220 rollback transaction rs_logexec 221 return (1) 222 end 223 224 225 226 commit transaction rs_logexec 227 228 /* 229 ** 17489, "Group has been dropped." 230 */ 231 exec sp_getmessage 17489, @msg output 232 print @msg 233 234 return (0) 235 236 clean_all: 237 rollback transaction rs_logexec 238 return (1) 239
exec sp_procxmode 'sp_dropgroup', 'AnyMode' go Grant Execute on sp_dropgroup to public go
| RESULT SETS | |
| sp_dropgroup_rset_002 | |
| sp_dropgroup_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED 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) writes table sybsystemprocs..sysprotects reads table master..syssrvroles (1) reads table sybsystemprocs..sysobjects read_writes table sybsystemprocs..sysusers calls proc sybsystemprocs..sp_ha_check_certified  reads table tempdb..sysobjects (1)  |
