hotfix.sql

This project is to design the automated system for the inventory of Al-Ahmad Cotton and Oil Mills si

begin
	insert into spt_server_info
		values (16,'IDENTIFIER_CASE','SENSITIVE')
end
insert into spt_server_info
	values (17,'TX_ISOLATION','2')
if (charindex('6.00', @@version) > 0 or
	charindex('6.50', @@version) > 0 or
	charindex('7.00', @@version) > 0) 
begin	/*	Add 6.0 collation sequence */
	insert into spt_server_info
		select 18,'COLLATION_SEQ',
			'charset='+t2.name+' sort_order='+t1.name
			+' charset_num='+rtrim(convert(char(4),t1.csid))+
			' sort_order_num='+rtrim(convert(char(4),t1.id))
		from syscharsets t1, syscharsets t2, sysconfigures t3
		where t1.csid=t2.id and t1.id=t3.value and t3.config=1123
end
else 
begin	/*	Add 4.2x collation sequence */
	insert into spt_server_info
		select 18,'COLLATION_SEQ',
			'charset='+t2.name+' sort_order='+t1.name
			+' charset_num='+rtrim(convert(char(4),t1.csid))+
			' sort_order_num='+rtrim(convert(char(4),t1.id))
		from syscharsets t1, syscharsets t2, sysconfigures t3
		where t1.csid=t2.id and t1.id=t3.value and t3.config=123
end
insert into spt_server_info
	values (19,'SAVEPOINT_SUPPORT','Y')
insert into spt_server_info
	values (20,'MULTI_RESULT_SETS','Y')
insert into spt_server_info
	values (22,'ACCESSIBLE_TABLES','Y')
go

insert into spt_server_info
	values (100,'USERID_LENGTH','30')
insert into spt_server_info
	values (101,'QUALIFIER_TERM','database')
insert into spt_server_info
	values (102,'NAMED_TRANSACTIONS','Y')
insert into spt_server_info
	values (103,'SPROC_AS_LANGUAGE','Y')
insert into spt_server_info
	values (104,'ACCESSIBLE_SPROC','Y')
insert into spt_server_info
	values (105,'MAX_INDEX_COLS','16')
insert into spt_server_info
	values (106,'RENAME_TABLE','Y')
insert into spt_server_info
	values (107,'RENAME_COLUMN','Y')

if (charindex('8.00', @@version) > 0)
begin
	/* Columns may be dropped on 8.0 or later */
	insert into spt_server_info
		values (108,'DROP_COLUMN','Y')
end
else
begin
	insert into spt_server_info
		values (108,'DROP_COLUMN','N')
end

if (charindex('8.00', @@version) > 0)
begin
	/* Columns size may be changed on 8.0 or later */
	insert into spt_server_info
		values (109,'INCREASE_COLUMN_LENGTH','Y')
end
else
begin
	insert into spt_server_info
		values (109,'INCREASE_COLUMN_LENGTH','N')
end

if (charindex('6.50', @@version) = 0 and
	charindex('7.00', @@version) = 0 and
	charindex('8.00', @@version) = 0)
begin
	insert into spt_server_info
		values (110,'DDL_IN_TRANSACTION','N')
end
else
begin
	insert into spt_server_info
		values (110,'DDL_IN_TRANSACTION','Y')
end


if (charindex('8.00', @@version) > 0)
begin
	/* Descending indexes allowed on 8.0 or later */
	insert into spt_server_info
		values (111,'DESCENDING_INDEXES','Y')
end
else
begin
	insert into spt_server_info
		values (111,'DESCENDING_INDEXES','N')
end

insert into spt_server_info
	values (112,'SP_RENAME','Y')
insert into spt_server_info
	values (113,'REMOTE_SPROC','Y')
insert into spt_server_info
	values (500,'SYS_SPROC_VERSION','8.00.178')
go

if (charindex('7.00', @@version) > 0 or
	charindex('8.00', @@version) > 0)
begin	/*	Update values for 8.0 server */
	update spt_server_info set attribute_value = '128'
		where attribute_id in (12,13,14,15,100)
end
go

grant select on spt_server_info to public
go

print 'creating sp_column_privileges'
go

/*	Procedure for pre 6.50 server */
CREATE PROCEDURE sp_column_privileges (
			@table_name 		varchar(32),
			@table_owner		varchar(32) = null,
			@table_qualifier	varchar(32) = null,
			@column_name		varchar(96) = null)	/* 3*32 */
as

	set nocount on
	declare @table_id	 int
	DECLARE @full_table_name	varchar(65)	/* 2*32 + 1 */
	declare @low smallint					 /* range of userids to check */
	declare @high smallint
	declare @owner_uid smallint

    select @low = 0, @high = 32767

	if @column_name is null /*	If column name not supplied, match all */
		select @column_name = '%'

	if @table_qualifier is not null
    begin
		if db_name() <> @table_qualifier
		begin	/* If qualifier doesn't match current database */
			raiserror 20001 '~~Rush_5~~'
			return
		end
    end
	if @table_owner is null
	begin	/* If unqualified table name */
		SELECT @full_table_name = @table_name
    end
    else
	begin	/* Qualified table name */
		SELECT @full_table_name = @table_owner + '.' + @table_name
    end
	/*	Get Object ID */
	select @table_id = object_id(@full_table_name)

	if (@@trancount <> 0)
	begin	/* If inside a transaction */
		raiserror 20003 '~~Rush_6~~'
		return
    end

    /*
    ** We need to create a table which will contain a row for every row to
    ** be returned to the client.
    */

	create table #column_priv1(
		COLUMN_NAME 			varchar(32) NOT NULL,
		grantor 				smallint NOT NULL,
		grantee 				smallint NOT NULL,
		select_privilege		bit,
		select_grantable		bit,
		insert_privilege		bit,
		insert_grantable		bit,
		update_privilege		bit,
		update_grantable		bit,
		references_privilege	bit,
		references_grantable	bit,
		uid 					smallint NOT NULL,
		gid 					smallint NOT NULL)

/*
** insert a row for the table owner (who has all permissions)
*/
	select @owner_uid = (
		select uid
		from sysobjects
		where id = @table_id)

	if (charindex('6.00', @@version) > 0)
	begin
		insert into #column_priv1
			select
				c.name,
				u.uid,
				@owner_uid,
				0,
				1,
				0,
				1,
				0,
				1,
				0,
				1,
				@owner_uid,
				0
			from syscolumns c, sysusers u
			where id = @table_id
				and c.number = 0
				and u.uid = 1		/* grantor is dbo of database */
	end
	else
	begin
		insert into #column_priv1
			select
				c.name,
				u.uid,
				@owner_uid,
				0,
				1,
				0,
				1,
				0,
				1,
				0,
				0,
				@owner_uid,
				0
			from syscolumns c, sysusers u
			where id = @table_id
				and c.number = 0
				and u.uid = 1		/* grantor is dbo of database */
	end
/*
** now stick in a row for every column for every user in the database
** we will need to weed out those who have no permissions later
** (and yes this is a cartesion product: the uid field in sysprotects
** can also have a group id, in which case we need to extend those
** privileges to all group members).
*/

    insert into #column_priv1
		select distinct
			c.name,
			o.uid,
			u.uid,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			u.uid,
			u.gid
		from sysusers u, syscolumns c, sysobjects o
		where o.id = @table_id
			and c.id = o.id
			and c.number = 0
			and u.gid <> u.uid
			and u.uid <> @owner_uid

    /*
    ** we need to create another temporary table to contain all the various
    ** protection information for the table in question
    */
	create table #protects (
				uid 		smallint NOT NULL,
				grantor		smallint NOT NULL,
				action		tinyint NOT NULL,
				protecttype tinyint NOT NULL,
				name		varchar(32) NOT NULL)

    insert into #protects
		select
			p.uid,
			p.uid,
			p.action,
			p.protecttype,
			isnull(col_name(id, c.number), '~All')
			from
				sysprotects p,
				master.dbo.spt_values c,
				master.dbo.spt_values a,
				master.dbo.spt_values b
			where
				convert(tinyint, substring(isnull(p.columns, 0x1), c.low, 1))
					& c.high <> 0
					and c.number <= (
						select count(*)
						from syscolumns
						where id = @table_id)
				and c.type = 'P'
				and a.type = 'T'
				and a.number = p.action
				and p.action in (193,195,197,26)
				and b.type = 'T'
				and b.number = p.protecttype
				and p.id = @table_id
				and p.uid between @low and @high


    update #column_priv1
	set select_privilege = 1
		from #protects p
	where
		p.protecttype = 205
		and p.action = 193
		and (p.name = #column_priv1.COLUMN_NAME
			or name = '~All')
		and (p.uid = 0
			or p.uid = #column_priv1.gid
			or p.uid = #column_priv1.uid)
		and not exists (
			select * from #protects
			where
				protecttype = 206
				and action = 193
				and (name = #column_priv1.COLUMN_NAME
					or name = '~All')
				and ( uid = 0
					or uid = #column_priv1.gid
					or uid = #column_priv1.uid))

    update #column_priv1
	set insert_privilege = 1
		from #protects p
	where
		p.protecttype = 205
		and p.action = 195
		and (p.name = #column_priv1.COLUMN_NAME
			or name = '~All')
		and (p.uid = 0
			or p.uid = #column_priv1.gid
			or p.uid = #column_priv1.uid)
		and not exists (
			select * from #protects
			where
				protecttype = 206
				and action = 195
				and (name = #column_priv1.COLUMN_NAME
       				or name = '~All')
				and (uid = 0
					or uid = #column_priv1.gid
					or uid = #column_priv1.uid))

    update #column_priv1
	set update_privilege = 1
		from #protects p
	where
		p.protecttype = 205
		and p.action = 197
		and (p.name = #column_priv1.COLUMN_NAME
			or name = '~All')
		and (p.uid = 0
			or p.uid = #column_priv1.gid
			or p.uid = #column_priv1.uid)
		and not exists (
			select * from #protects
				where protecttype = 206
				and action = 197
				and (name = #column_priv1.COLUMN_NAME
					or name = '~All')
				and (uid = 0
					or uid = #column_priv1.gid
					or uid = #column_priv1.uid))

    update #column_priv1
	set references_privilege = 1
		from #protects p
	where
		p.protecttype = 205
		and p.action = 26
		and (p.name = #column_priv1.COLUMN_NAME
			or name = '~All')
		and (p.uid = 0
			or p.uid = #column_priv1.gid
			or p.uid = #column_priv1.uid)
		and not exists (
			select * from #protects
				where protecttype = 206
				and action = 26
				and (name = #column_priv1.COLUMN_NAME
					or name = '~All')
				and (uid = 0
					or uid = #column_priv1.gid
					or uid = #column_priv1.uid))

    update #column_priv1
	set select_grantable = 1
		from #protects p
	where
		p.protecttype = 204
		and p.action = 193
		and (p.name = #column_priv1.COLUMN_NAME
			or name = '~All')
		and (p.uid = 0
			or p.uid = #column_priv1.gid
			or p.uid = #column_priv1.uid)
		and not exists (
			select * from #protects
			where
				protecttype = 206
				and action = 193
				and (name = #column_priv1.COLUMN_NAME
					or name = '~All')
				and ( uid = 0
					or uid = #column_priv1.gid
					or uid = #column_priv1.uid))

    update #column_priv1
	set insert_grantable = 1
		from #protects p
	where
		p.protecttype = 204
		and p.action = 195
		and (p.name = #column_priv1.COLUMN_NAME
			or name = '~All')
		and (p.uid = 0
			or p.uid = #column_priv1.gid
			or p.uid = #column_priv1.uid)
		and not exists (
			select * from #protects
			where
				protecttype = 206
				and action = 195
				and (name = #column_priv1.COLUMN_NAME
					or name = '~All')
				and ( uid = 0
					or uid = #column_priv1.gid
					or uid = #column_priv1.uid))

    update #column_priv1
	set update_grantable = 1
		from #protects p
	where
		p.protecttype = 204
		and p.action = 197