Please use FireFox to view this page

create procedure sp_addrole @rolename sysname, -- name of new role @ownername sysname = 'dbo' -- name of owner of new role as -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES -- set nocount on declare @ret int, -- return value of sp call @uid smallint, @owner smallint -- CHECK PERMISSIONS -- if (not is_member('db_securityadmin') = 1) and (not is_member('db_owner') = 1) begin dbcc auditevent (111, 1, 0, NULL, NULL, @rolename, NULL) raiserror(15247,-1,-1) return (1) end else begin dbcc auditevent (111, 1, 1, NULL, NULL, @rolename, NULL) end -- DISALLOW USER TRANSACTION -- set implicit_transactions off if (@@trancount > 0) begin raiserror(15002,-1,-1,'sp_addrole') return (1) end -- RESOLVE OWNER NAME -- select @owner = uid from sysusers where name = @ownername and isaliased = 0 AND uid NOT IN (0,3,4) --public/INFO_SCHEMA/etc can't own role if @owner is null begin raiserror(15008,-1,-1,@ownername) return (1) end -- VALIDATE ROLE NAME -- execute @ret = sp_validname @rolename if @ret <> 0 return (1) if (charindex('\', @rolename) > 0) begin raiserror(15006,-1,-1,@rolename) return (1) end if @rolename = 'sys' raiserror(15355,-1,-1) -- ERROR IF SYSUSERS NAME ALREADY EXISTS -- if user_id(@rolename) is not null OR @rolename IN ('system_function_schema','INFORMATION_SCHEMA') begin if exists (select name from sysusers where issqlrole = 1 and name = @rolename) raiserror(15363,-1,-1,@rolename) else raiserror(15023,-1,-1,@rolename) return (1) end -- OBTAIN NEW ROLE UID (RESERVE 16384-16399) -- if user_name(16400) IS NULL select @uid = 16400 else select @uid = min(uid)+1 from sysusers where uid >= 16400 and uid < (32767 - 1) -- stay in role range and user_name(uid+1) is null -- uid not in use if @uid is null begin raiserror(15065,-1,-1) return (1) end -- INSERT THE ROW INTO SYSUSERS -- insert into sysusers values (@uid, 0, @rolename, NULL, 0x00, getdate(), getdate(), @owner, NULL) -- FINALIZATION: PRINT/RETURN SUCCESS -- if @@error <> 0 return (1) raiserror(15424,-1,-1) return (0) -- sp_addrole