-- Name: sp_add_data_file_recover_suspect_db
-- Purpose: Adds a data file to a suspect database and runs
-- 		recovery on the database.  This SP should only be used
--		on databases that have been marked suspect due to
--		insufficient data (error 1105) or log (error 9002) space.
--
create procedure sp_add_data_file_recover_suspect_db
	@dbName 	sysname			-- database name
	,@filegroup	nvarchar(260)		-- file group for new file
	,@name		nvarchar(260)		-- logical file name
	,@filename	nvarchar(260)		-- OS file name
	,@size		nvarchar(20) 	= NULL	-- initial file size
	,@maxsize	nvarchar(20) 	= NULL	-- maximum file size
	,@filegrowth	nvarchar(20) 	= NULL	-- growth increment
as
execute sp_add_file_recover_suspect_db @dbName, 'DATA', @filegroup, @name, @filename, @size, @maxsize, @filegrowth
go

raiserror(15339,-1,-1,'sp_add_log_file_recover_suspect_db')
Go
--
-- Name: sp_add_log_file_recover_suspect_db
-- Purpose: Adds a log file to a suspect database and runs
-- 		recovery on the database.  This SP should only be used
--		on databases that have been marked suspect due to
--		insufficient data (error 1105) or log (error 9002) space.
--
create procedure sp_add_log_file_recover_suspect_db
	@dbName 	sysname			-- database name
	,@name		nvarchar(260)		-- logical file name
	,@filename	nvarchar(260)		-- OS file name
	,@size		nvarchar(20) 	= NULL	-- initial file size
	,@maxsize	nvarchar(20) 	= NULL	-- maximum file size
	,@filegrowth	nvarchar(20) 	= NULL	-- growth increment
as
execute sp_add_file_recover_suspect_db @dbName, 'LOG', NULL, @name, @filename, @size, @maxsize, @filegrowth
go


raiserror(15339,-1,-1,'sp_spaceused')
go
create procedure sp_spaceused --- 1996/08/20 17:01
@objname nvarchar(776) = null,		-- The object we want size on.
@updateusage varchar(5) = false		-- Param. for specifying that
					-- usage info. should be updated.
as

declare @id	int			-- The object id of @objname.
declare @type	character(2) -- The object type.
declare	@pages	int			-- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage	dec(15,0)
declare @pagesperMB		dec(15,0)

/*Create temp tables before any DML to ensure dynamic
**  We need to create a temp table to do the calculation.
**  reserved: sum(reserved) where indid in (0, 1, 255)
**  data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
**  indexp: sum(used) where indid in (0, 1, 255) - data
**  unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
create table #spt_space
(
	rows		int null,
	reserved	dec(15) null,
	data		dec(15) null,
	indexp		dec(15) null,
	unused		dec(15) null
)

/*
**  Check to see if user wants usages updated.
*/

if @updateusage is not null
	begin
		select @updateusage=lower(@updateusage)

		if @updateusage not in ('true','false')
			begin
				raiserror(15143,-1,-1,@updateusage)
				return(1)
			end
	end
/*
**  Check to see that the objname is local.
*/
if @objname IS NOT NULL
begin

	select @dbname = parsename(@objname, 3)

	if @dbname is not null and @dbname <> db_name()
		begin
			raiserror(15250,-1,-1)
			return (1)
		end

	if @dbname is null
		select @dbname = db_name()

	/*
	**  Try to find the object.
	*/
	select @id = null
	select @id = id, @type = xtype
		from sysobjects
			where id = object_id(@objname)

	/*
	**  Does the object exist?
	*/
	if @id is null
		begin
			raiserror(15009,-1,-1,@objname,@dbname)
			return (1)
		end


	if not exists (select * from sysindexes
				where @id = id and indid < 2)

		if      @type in ('P ','D ','R ','TR','C ','RF') --data stored in sysprocedures
				begin
					raiserror(15234,-1,-1)
					return (1)
				end
		else if @type = 'V ' -- View => no physical data storage.
				begin
					raiserror(15235,-1,-1)
					return (1)
				end
		else if @type in ('PK','UQ') -- no physical data storage. --?!?! too many similar messages
				begin
					raiserror(15064,-1,-1)
					return (1)
				end
		else if @type = 'F ' -- FK => no physical data storage.
				begin
					raiserror(15275,-1,-1)
					return (1)
				end
end

/*
**  Update usages if user specified to do so.
*/

if @updateusage = 'true'
	begin
		if @objname is null
			dbcc updateusage(0) with no_infomsgs
		else
			dbcc updateusage(0,@objname) with no_infomsgs
		print ' '
	end


set nocount on

/*
**  If @id is null, then we want summary data.
*/
/*	Space used calculated in the following way
**	@dbsize = Pages used
**	@bytesperpage = d.low (where d = master.dbo.spt_values) is
**	the # of bytes per page when d.type = 'E' and
**	d.number = 1.
**	Size = @dbsize * d.low / (1048576 (OR 1 MB))
*/
if @id is null
begin
	select @dbsize = sum(convert(dec(15),size))
		from dbo.sysfiles
		where (status & 64 = 0)

	select @logsize = sum(convert(dec(15),size))
		from dbo.sysfiles
		where (status & 64 <> 0)

	select @bytesperpage = low
		from master.dbo.spt_values
		where number = 1
			and type = 'E'
	select @pagesperMB = 1048576 / @bytesperpage

	select  database_name = db_name(),
		database_size =
			ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),
		'unallocated space' =
			ltrim(str((@dbsize -
				(select sum(convert(dec(15),reserved))
					from sysindexes
						where indid in (0, 1, 255)
				)) / @pagesperMB,15,2)+ ' MB')

	print ' '
	/*
	**  Now calculate the summary data.
	**  reserved: sum(reserved) where indid in (0, 1, 255)
	*/
	insert into #spt_space (reserved)
		select sum(convert(dec(15),reserved))
			from sysindexes
				where indid in (0, 1, 255)

	/*
	** data: sum(dpages) where indid < 2
	**	+ sum(used) where indid = 255 (text)
	*/
	select @pages = sum(convert(dec(15),dpages))
			from sysindexes
				where indid < 2
	select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
		from sysindexes
			where indid = 255
	update #spt_space
		set data = @pages


	/* index: sum(used) where indid in (0, 1, 255) - data */
	update #spt_space
		set indexp = (select sum(convert(dec(15),used))
				from sysindexes
					where indid in (0, 1, 255))
			    - data

	/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
	update #spt_space
		set unused = reserved
				- (select sum(convert(dec(15),used))
					from sysindexes
						where indid in (0, 1, 255))

	select reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
				' ' + 'KB'),
		data = ltrim(str(data * d.low / 1024.,15,0) +
				' ' + 'KB'),
		index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
				' ' + 'KB'),
		unused = ltrim(str(unused * d.low / 1024.,15,0) +
				' ' + 'KB')
		from #spt_space, master.dbo.spt_values d
		where d.number = 1
			and d.type = 'E'
end

/*
**  We want a particular object.
*/
else
begin
	/*
	**  Now calculate the summary data.
	**  reserved: sum(reserved) where indid in (0, 1, 255)
	*/
	insert into #spt_space (reserved)
		select sum(reserved)
			from sysindexes
				where indid in (0, 1, 255)
					and id = @id

	/*
	** data: sum(dpages) where indid < 2
	**	+ sum(used) where indid = 255 (text)
	*/
	select @pages = sum(dpages)
			from sysindexes
				where indid < 2
					and id = @id
	select @pages = @pages + isnull(sum(used), 0)
		from sysindexes
			where indid = 255
				and id = @id
	update #spt_space
		set data = @pages


	/* index: sum(used) where indid in (0, 1, 255) - data */
	update #spt_space
		set indexp = (select sum(used)
				from sysindexes
					where indid in (0, 1, 255)
						and id = @id)
			    - data

	/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
	update #spt_space
		set unused = reserved
				- (select sum(used)
					from sysindexes
						where indid in (0, 1, 255)
							and id = @id)
	update #spt_space
		set rows = i.rows
			from sysindexes i
				where i.indid < 2
					and i.id = @id

	select name = object_name(@id),
		rows = convert(char(11), rows),
		reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
				' ' + 'KB'),
		data = ltrim(str(data * d.low / 1024.,15,0) +
				' ' + 'KB'),
		index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
				' ' + 'KB'),
		unused = ltrim(str(unused * d.low / 1024.,15,0) +
				' ' + 'KB')
	from #spt_space, master.dbo.spt_values d
		where d.number = 1
			and d.type = 'E'
end

return (0) -- sp_spaceused
go

checkpoint
go

raiserror(15339,-1,-1,'sp_sqlexec')

-- sp_sqlexec should not be allowed to update system catalog
exec sp_configure 'allow updates',0
reconfigure with override
go

create procedure sp_sqlexec --- 1996/04/08 00:00
    @p1 text
as
exec(@p1)
go

exec sp_configure 'allow updates',1
reconfigure with override

go


raiserror(15339,-1,-1,'sp_unbindefault')
go
create procedure sp_unbindefault --- 1996/08/13 13:34
@objname nvarchar(776),         /* table/column or datatype name */
@futureonly varchar(15) = NULL   /* flag to indicate extent of binding */
as

declare @futurevalue varchar(15) /* the value of @futureonly that causes
               ** the binding to be limited */

declare
	@vc1			nvarchar(517)
declare
	@tab_id			integer
	,@cur_tab_id	integer
	,@colid			integer
	,@cdefault		integer
	,@olddefault	integer
	,@xusertype		smallint

	,@UnqualObj		sysname
	,@QualObj1		sysname
	,@QualObj2		sysname
	,@QualObj3		sysname

set cursor_close_on_commit	off
select @futurevalue = 'futureonly'  /* initialize @futurevalue */

/*
**  When a default or rule is bound to a user-defined datatype, it is also
**  bound, by default, to any columns of the user datatype that are currently
**  using the existing default or rule as their default or rule.  This default
**  action may be overridden by setting @futureonly = @futurevalue when the
**  procedure is invoked.  In this case existing columns with the user
**  datatype won't have their existing default or rule changed.
*/

-- get name parts --
select @UnqualObj = parsename(@objname, 1),
        @QualObj1 = parsename(@objname, 2),
        @QualObj2 = parsename(@objname, 3),
        @QualObj3 = parsename(@objname, 4)

IF (@UnqualObj is NULL OR @QualObj3 is not null)
   begin
   raiserror(15253,-1,-1,@objname)
   return (1)
   end


------------------  Verify database.

if (@QualObj2 is not null and @QualObj1 is null)
	begin
		raiserror(15084,-1,-1)
		return (1)
	end

/*
**  If @objname is of the form tab.col then we are unbinding a column.
**  Otherwise its a datatype.  In the column case, we need to extract
**  and verify the table and column names and make sure the user owns
**  the table that is getting the default unbound.
*/
if @QualObj1 is not null
begin
	if (@QualObj2 is not null)
		select @vc1 = QuoteName(@QualObj2) + '.' + QuoteName(@QualObj1)
	else
		select @vc1 = QuoteName(@QualObj1)

   /*
   **  Find it and unbind it.
   */
   select @tab_id = c.id, @colid = c.colid, @cdefault = c.cdefault
   from syscolumns c, sysobjects o
      where c.id = o.id
         and c.name = @UnqualObj
         and o.id = object_id(@vc1,'local')
         and o.xtype = N'U '

	if @tab_id is null
	begin
		 raiserror(15104,-1,-1,@QualObj1,@UnqualObj)
		 return(1)
	end

	if @cdefault = 0
	begin
		raiserror(15236,-1,-1,@objname)
		return(1)
	end

	if exists
	(select	*
		from	sysobjects o
		where	@cdefault	= o.id
			and	@tab_id		= o.parent_obj)
		begin
			raiserror(15049,-1,-1, @objname)
			return (1)
		end

	BEGIN TRANSACTION txn_unbindefault_1

         /*
         **  Since binding a default is a schema change,
         **  update schema count
         **  for the object in the sysobjects table.
         */
		dbcc LockObjectSchema(@vc1) --- Undocu. Locks out other schema changes until commit, and increments sysobjects.schema_ver.

		update syscolumns set cdefault = 0
			from syscolumns where id = @tab_id and colid = @colid

	COMMIT TRANSACTION txn_bindefault_1

	raiserror(15519,-1,-1)
	return (0)

end

else

begin
   /*
   **  We're unbinding to a user type.  In this case, the @objname
   **  is really the name of the user datatype.
   **  When we unbind to a user type, any existing columns get changed
   **  to the new binding unless their current binding is not equal
   **  to the current binding for the usertype or if they set the
   **  @futureonly parameter to @futurevalue.
   */

   /*
   **  Get the current default for the datatype.
   */
   select @olddefault = tdefault, @xusertype = xusertype
		from systypes where name = @UnqualObj and xusertype > 256
		AND (is_member('db_owner') = 1 OR is_member('db_ddladmin') = 1 OR is_member(user_name(uid))=1)

   if @olddefault is null
      begin
         raiserror(15036,-1,-1,@UnqualObj)
         return (1)
      end

   if @olddefault = 0
      begin
         raiserror(15237,-1,-1,@UnqualObj)
         return (1)
      end

   update systypes set tdefault = 0
      from systypes
      where xusertype = @xusertype

   raiserror(15520,-1,-1)

   /*
   **  Now see if there are any columns with the usertype that
   **  need the new binding.
   */
   select @futureonly = lower(@futureonly)
   if isnull(@futureonly, ' ') <> @futurevalue
   begin
		declare ms_crs_unbindefault_1 cursor local static for
			select
			distinct
				 c.id
				,c.colid
			from	 syscolumns c JOIN sysobjects o ON c.id = o.id AND o.xtype = N'U '
         		where  c.xusertype = @xusertype
            		and c.cdefault = @olddefault
			order by c.id
					for read only

		open ms_crs_unbindefault_1

		fetch next from ms_crs_unbindefault_1 into
			@tab_id
			,@colid

		BEGIN TRANSACTION txn_unbindefault_2

			while @@fetch_status = 0
			begin

				select @vc1 = quotename(user_name(OBJECTPROPERTY(@tab_id,'OwnerId'))) + '.'
				+ quotename(object_name(@tab_id))

				dbcc LockObjectSchema(@vc1) --- Undocu. Locks out other schema changes until commit, and increments sysobjects.schema_ver.

				select @cur_tab_id = @tab_id

				while @cur_tab_id = @tab_id and @@fetch_status = 0
				begin
  					update syscolumns set cdefault = 0
					from syscolumns
					where id = @tab_id and colid = @colid

					fetch next from ms_crs_unbindefault_1 into
						@tab_id
						,@colid
				end
			end --loop 3      /*

		COMMIT TRANSACTION txn_unbindefault_2

		deallocate ms_crs_unbindefault_1

		raiserror(15521,-1,-1)
	end
end

return (0) -- sp_unbindefault
go

raiserror(15339,-1,-1,'sp_unbindrule')
go
create procedure sp_unbindrule --- 1996/08/13 13:33
@objname nvarchar(776),         /* table/column or datatype name */
@futureonly varchar(15) = NULL      /* flag to indicate extent of binding */
as

declare @oldrule int /* current rule for type */
declare @tabname sysname     /* name of table */
declare @colname sysname     /* name of column */
declare @futurevalue varchar(15) /* the value of @futureonly that causes
                           ** the binding to be limited */

declare
	@vc1			nvarchar(517)
declare
	 @obj_id		integer
	,@cur_tab_id	integer
	,@colid			integer
	,@domain		integer
	,@xusertype		smallint

	,@owner_name	sysname
	,@obj_name		sysname

	,@UnqualObj			sysname
	,@QualObj1			sysname
	,@QualObj2			sysname
	,@QualObj3			sysname

set cursor_close_on_commit	off

select @futurevalue = 'futureonly'  /* initialize @futurevalue */

/*
**  When a default or rule is bound to a user-defined datatype, it is also
**  bound, by default, to any columns of the user datatype that are currently
**  using the existing default or rule as their default or rule.  This default
**  action may be overridden by setting @futureonly = @futurevalue when the
**  procedure is invoked.  In this case existing columns with the user
**  datatype won't have their existing default or rule changed.
*/

-- get name parts --
select @UnqualObj = parsename(@objname, 1),
        @QualObj1 = parsename(@objname, 2),
        @QualObj2 = parsename(@objname, 3),
        @QualObj3 = parsename(@objname, 4)

IF (@UnqualObj is NULL OR @QualObj3 is not null)
   begin
   raiserror(15253,-1,-1,@objname)
   return (1)
   end


------------------  Verify database.

if (@QualObj2 is not null and @QualObj1 is null)
	begin
		raiserror(15084,-1,-1)
		return (1)
	end

/*
**  If @objname is of the form tab.col then we are unbinding a column.
**  Otherwise its a datatype.  In the column case, we need to extract
**  and verify the table and column names and make sure the user owns
**  the table that is getting the default unbound.
*/
if @QualObj1 is not null
begin
	if (@QualObj2 is not null)
		select @vc1 = QuoteName(@QualObj2) + '.' + QuoteName(@QualObj1)
	else
		select @vc1 = QuoteName(@QualObj1)

	select	@obj_id = c.id,	@colid = c.colid,	@domain = c.domain
		from syscolumns c, sysobjects o
	where c.id = o.id
	and c.name = @UnqualObj
	and o.id = object_id(@vc1,'local')
	and o.xtype = N'U '

	if @obj_id is null
	begin
		raiserror(15104,-1,-1,@QualObj1,@UnqualObj)
		return (1)
	end

	if @domain = 0
	begin
		raiserror(15238,-1,-1,@objname)
		return (1)
	end

	BEGIN TRANSACTION txn_unbindrule_1

		/*
		**  Update schema count
		**  for the object in the sysobjects table.
		*/

		dbcc LockObjectSchema(@vc1) -- Locks Object and increments schema_ver.

		update syscolumns set domain = 0
			from syscolumns c where id = @obj_id and colid = @colid

	COMMIT TRANSACTION txn_unbindrule_1

	raiserror(15522,-1,-1)

end
else
begin

	select @oldrule = domain, @xusertype = xusertype
		from systypes where name = @UnqualObj and xusertype > 256
		AND (is_member('db_owner') = 1 OR is_member('db_ddladmin') = 1 OR is_member(user_name(uid))=1)

	if @xusertype is null
	begin
		raiserror(15036,-1,-1,@UnqualObj)
		return (1)
	end

	if @oldrule = 0
	begin
		raiserror(15239,-1,-1,@UnqualObj)
		return (1)
	end

	update systypes set domain = 0
	from systypes
	where xusertype = @xusertype

	raiserror(15523,-1,-1)

	select @futureonly = lower(@futureonly)
	if isnull(@futureonly, ' ') <> @futurevalue
	begin

		declare ms_crs_unbindrule_1 cursor local static for
		select
			distinct
			 o.id
			,user_name(o.uid)
			,o.name
			,c.colid
		from	syscolumns c
			,sysobjects o
         where o.id = c.id and o.xtype = N'U '
            and c.xusertype = @xusertype
            and c.domain = @oldrule
	    order by o.id
            for read only

		open ms_crs_unbindrule_1


        BEGIN TRANSACTION txn_unbindrule_2

			fetch next from ms_crs_unbindrule_1 into
			@obj_id
			,@owner_name
			,@obj_name
			,@colid

			while @@fetch_status = 0
			begin

				select @vc1 = quotename(@owner_name) + '.' + quotename(@obj_name)

				dbcc LockObjectSchema(@vc1) --- Undocu. Locks out other schema changes until commit, and increments sysobjects.schema_ver.

				select @cur_tab_id = @obj_id

				while @cur_tab_id = @obj_id and @@fetch_status = 0
				begin
  					update syscolumns set domain = 0
					from syscolumns
					where id = @obj_id and colid = @colid

					fetch next from ms_crs_unbindrule_1 into
					@obj_id
					,@owner_name
					,@obj_name
					,@colid
				end
			end

		COMMIT TRANSACTION txn_unbindrule_2
		deallocate ms_crs_unbindrule_1
		raiserror(15524,-1,-1)

	end
end

return (0)	--sp_unbindrule
go


checkpoint
go

--
raiserror(15339,-1,-1,'sp_who')
go
create procedure sp_who  --- 1995/11/28 15:48
       @loginame sysname = NULL --or 'active'
as

declare	 @spidlow	int,
		 @spidhigh	int,
		 @spid		int,
		 @sid		varbinary(85)

select	 @spidlow	=     0
		,@spidhigh	= 32767


if (	@loginame is not NULL
   AND	upper(@loginame) = 'ACTIVE'
   )
	begin

	select spid , ecid, status
              ,loginame=rtrim(loginame)
	      ,hostname ,blk=convert(char(5),blocked)
	      ,dbname = case
						when dbid = 0 then null
						when dbid <> 0 then db_name(dbid)
					end
		  ,cmd
	from  master.dbo.sysprocesses
	where spid >= @spidlow and spid <= @spidhigh AND
	      upper(cmd) <> 'AWAITING COMMAND'

	return (0)
	end

if (@loginame is not NULL
   AND	upper(@loginame) <> 'ACTIVE'
   )
begin
	if (@loginame like '[0-9]%')	-- is a spid.
	begin
		select @spid = convert(int, @loginame)
		select spid, ecid, status,
			   loginame=rtrim(loginame),
			   hostname,blk = convert(char(5),blocked),
			   dbname = case
							when dbid = 0 then null
							when dbid <> 0 then db_name(dbid)
						end
			  ,cmd
		from  master.dbo.sysprocesses
		where spid = @spid
	end
	else
	begin
		select @sid = suser_sid(@loginame)
		if (@sid is null)
		begin
			raiserror(15007,-1,-1,@loginame)
			return (1)
		end
		select spid, ecid, status,
			   loginame=rtrim(loginame),
			   hostname ,blk=convert(char(5),blocked),
			   dbname = case
							when dbid = 0 then null
							when dbid <> 0 then db_name(dbid)
						end
			   ,cmd
		from  master.dbo.sysprocesses
		where sid = @sid
	end
	return (0)
end


/* loginame arg is null */
select spid,
	   ecid,
	   status,
       loginame=rtrim(loginame),
	   hostname,
	   blk=convert(char(5),blocked),
	   dbname = case
					when dbid = 0 then null
					when dbid <> 0 then db_name(dbid)
				end
	   ,cmd
from  master.dbo.sysprocesses
where spid >= @spidlow and spid <= @spidhigh


return (0) -- sp_who
go



raiserror(15339,-1,-1,'sp_who2')
go
CREATE PROCEDURE sp_who2  --- 1995/11/03 10:16
    @loginame     sysname = NULL
as

set nocount on

declare
    @retcode         int

declare
    @sidlow         varbinary(85)
   ,@sidhigh        varbinary(85)
   ,@sid1           varbinary(85)
   ,@spidlow         int
   ,@spidhigh        int

declare
    @charMaxLenLoginName      varchar(6)
   ,@charMaxLenDBName         varchar(6)
   ,@charMaxLenCPUTime        varchar(10)
   ,@charMaxLenDiskIO         varchar(10)
   ,@charMaxLenHostName       varchar(10)
   ,@charMaxLenProgramName    varchar(10)
   ,@charMaxLenLastBatch      varchar(10)
   ,@charMaxLenCommand        varchar(10)

declare
    @charsidlow              varchar(85)
   ,@charsidhigh             varchar(85)
   ,@charspidlow              varchar(11)
   ,@charspidhigh             varchar(11)

--------

select
    @retcode         = 0      -- 0=good ,1=bad.

--------defaults
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))

select
    @spidlow         = 0
   ,@spidhigh        = 32767

--------------------------------------------------------------
IF (@loginame IS     NULL)  --Simple default to all LoginNames.
      GOTO LABEL_17PARM1EDITED

--------

-- select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from master.dbo.syslogins where loginname = @loginame)
	select @sid1 = sid from master.dbo.syslogins where loginname = @loginame

IF (@sid1 IS NOT NULL)  --Parm is a recognized login name.
   begin
   select @sidlow  = suser_sid(@loginame)
         ,@sidhigh = suser_sid(@loginame)
   GOTO LABEL_17PARM1EDITED
   end

--------

IF (lower(@loginame) IN ('active'))  --Special action, not sleeping.
   begin
   select @loginame = lower(@loginame)
   GOTO LABEL_17PARM1EDITED
   end

--------

IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0)  --Is a number.
   begin
   select
             @spidlow   = convert(int, @loginame)
            ,@spidhigh  = convert(int, @loginame)
   GOTO LABEL_17PARM1EDITED
   end

--------

RaisError(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN


LABEL_17PARM1EDITED:


--------------------  Capture consistent sysprocesses.  -------------------

SELECT

  spid
 ,status
 ,sid
 ,hostname
 ,program_name
 ,cmd
 ,cpu
 ,physical_io
 ,blocked
 ,dbid
 ,convert(sysname, rtrim(loginame))
        as loginname
 ,spid as 'spid_sort'

 ,  substring( convert(varchar,last_batch,111) ,6  ,5 ) + ' '
  + substring( convert(varchar,last_batch,113) ,13 ,8 )
       as 'last_batch_char'

      INTO    #tb1_sysprocesses
      from master.dbo.sysprocesses   (nolock)



--------Screen out any rows?

IF (@loginame IN ('active'))
   DELETE #tb1_sysprocesses
         where   lower(status)  = 'sleeping'
         and     upper(cmd)    IN (
                     'AWAITING COMMAND'
                    ,'MIRROR HANDLER'
                    ,'LAZY WRITER'
                    ,'CHECKPOINT SLEEP'
                    ,'RA MANAGER'
                                  )

         and     blocked       = 0



--------Prepare to dynamically optimize column widths.


Select
    @charsidlow     = convert(varchar(85),@sidlow)
   ,@charsidhigh    = convert(varchar(85),@sidhigh)
   ,@charspidlow     = convert(varchar,@spidlow)
   ,@charspidhigh    = convert(varchar,@spidhigh)



SELECT
             @charMaxLenLoginName =
                  convert( varchar
                          ,isnull( max( datalength(loginname)) ,5)
                         )

            ,@charMaxLenDBName    =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)
                         )

            ,@charMaxLenCPUTime   =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)
                         )

            ,@charMaxLenDiskIO    =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)
                         )

            ,@charMaxLenCommand  =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)
                         )

            ,@charMaxLenHostName  =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)
                         )

            ,@charMaxLenProgramName =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)
                         )

            ,@charMaxLenLastBatch =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9)
                         )
      from
             #tb1_sysprocesses
      where
--             sid >= @sidlow
--      and    sid <= @sidhigh
--      and
             spid >= @spidlow
      and    spid <= @spidhigh



--------Output the report.


EXECUTE(
'
SET nocount off

SELECT
             SPID          = convert(char(5),spid)

            ,Status        =
                  CASE lower(status)
                     When ''sleeping'' Then lower(status)
                     Else                   upper(status)
                  END

            ,Login         = substring(loginname,1,' + @charMaxLenLoginName + ')

            ,HostName      =
                  CASE hostname
                     When Null  Then ''  .''
                     When '' '' Then ''  .''
                     Else    substring(hostname,1,' + @charMaxLenHostName + ')
                  END

            ,BlkBy         =
                  CASE               isnull(convert(char(5),blocked),''0'')
                     When ''0'' Then ''  .''
                     Else            isnull(convert(char(5),blocked),''0'')
                  END

            ,DBName        = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
            ,Command       = substring(cmd,1,' + @charMaxLenCommand + ')

            ,CPUTime       = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
            ,DiskIO        = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')

            ,LastBatch     = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')

            ,ProgramName   = substring(program_name,1,' + @charMaxLenProgramName + ')
            ,SPID          = convert(char(5),spid)  --Handy extra for right-scrolling users.
      from
             #tb1_sysprocesses  --Usually DB qualification is needed in exec().
      where
             spid >= ' + @charspidlow  + '
      and    spid <= ' + @charspidhigh + '

      -- (Seems always auto sorted.)   order by spid_sort

SET nocount on
'
)
/*****AKUNDONE: removed from where-clause in above EXEC sqlstr
             sid >= ' + @charsidlow  + '
      and    sid <= ' + @charsidhigh + '
      and
**************/


LABEL_86RETURN:


if (object_id('tempdb..#tb1_sysprocesses') is not null)
            drop table #tb1_sysprocesses

return @retcode -- sp_who2
go



checkpoint
go

---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------


---- Create following procs last, since they reference other procedures.



raiserror(15339,-1,-1,'sp_check_removable')
go
create procedure sp_check_removable @autofix varchar(4)
as

declare @dbosid varbinary (86)
declare @dbname sysname
declare @exec_stmt nvarchar(540)
declare @fgname sysname

select @dbname=db_name()

/* Verify that SA owns the database. */

select @dbosid = sid from master..sysdatabases where name = @dbname
if @dbosid <> 0x01
	if @autofix='auto'
	begin
		-- changing DBO to SA
		update sysdatabases set sid = 0x01
			where name = @dbname
		update sysusers set sid = 0x01
			where uid = 1
	end
	else
	begin
		raiserror(15258,-1,-1, @dbname)
		return(1)
	end

	-- USE CORRECT non-dbo/guest CHECKING
	declare @ret int
	exec @ret = sp_check_removable_sysusers @autofix
	if @ret <> 0
		return 1


	-- Run UPDATE STATISTICS on all user tables if there are
	-- no user defined filegroups
	if @autofix='auto' and
		(select count(*) from sysfilegroups) = 1
	begin
		select @exec_stmt = N'USE ' + quotename( @dbname , '[')
		+ N' exec sp_updatestats ''RESAMPLE'' '
		exec (@exec_stmt)
	end

	exec('dump tran '+@dbname+' with no_log')

	if (select count(*) from sysfilegroups) > 1
	begin
		if @autofix='auto'
		begin
			-- Mark any non-primary filegroups as READONLY
			DECLARE ms_crs_fg CURSOR LOCAL STATIC
			FOR SELECT groupname FROM sysfilegroups fg
				WHERE fg.groupid > 1 -- not primary
				AND fg.status & 0x8 = 0 -- not already readonly
				AND (SELECT count (*) FROM sysfiles f WHERE
				f.groupid = fg.groupid) > 0 -- has some files
			OPEN ms_crs_fg
			FETCH NEXT FROM ms_crs_fg INTO @fgname
			WHILE (@@FETCH_STATUS <> -1)
			BEGIN
				SELECT @exec_stmt = 'ALTER DATABASE '
				+ quotename( @dbname , '[')
				+ ' MODIFY FILEGROUP '
				+ quotename( @fgname , '[')
				+ ' READONLY'
				EXEC (@exec_stmt)
				FETCH NEXT FROM ms_crs_fg INTO @fgname
			END
			CLOSE ms_crs_fg
			DEALLOCATE ms_crs_fg
		end
		else
		begin
			if exists (SELECT groupname FROM sysfilegroups fg
				WHERE fg.groupid > 1 -- not primary
				AND fg.status & 0x8 = 0 -- not already readonly
				AND (SELECT count (*) FROM sysfiles f WHERE
				f.groupid = fg.groupid) > 0) -- has some files
			begin
				raiserror(15358,-1,-1)
				SELECT groupname FROM sysfilegroups fg
					WHERE fg.groupid > 1 -- not primary
					AND fg.status & 0x8 = 0 -- not already readonly
				return (-1)
			end
		end


	end
return(0)
go


raiserror(15339,-1,-1,'sp_certify_removable')
go
CREATE PROCEDURE sp_certify_removable  --1996/03/12 12:02

        @dbname sysname,
        @autofix nvarchar(4) = null
as

set nocount on

declare @ret_value int,
	@char_autofix varchar(25)

if (not (is_srvrolemember('sysadmin') = 1))  -- Make sure that it is the SA executing this.
        begin
                raiserror(15247,-1,-1)
                return(1)
        end


select @autofix = lower(@autofix)

if @autofix <> 'auto' and @autofix is not null
        begin
                raiserror(15255,-1,-1,@autofix)
                return(1)
        end


if @dbname is null      -- Show usage diagram if no dbname supplied.
        begin
                raiserror(15256,-1,-1)
                return(1)
        end


--See if DB exists.
if not exists (select * from master.dbo.sysdatabases where name = @dbname)
        begin
                raiserror(15010,-1,-1,@dbname)
                return(1)
        end


--Cannot take master, tempdb or model databases offline.
if lower(@dbname) in ('master','tempdb','model')
        begin
                raiserror(15266,-1,-1,@dbname)
                return(1)
        end


-- Will not be able to take db offline if user is in it.
if @dbname = db_name()
        begin
                raiserror(15257,-1,-1)
                return(1)
        end

-------------  Check things that exist only in the db.  -------------------
select @char_autofix =
	CASE
	   When @autofix IS NOT Null Then '''Auto'''
	   Else                           'Null'
	END

execute(
'use ' + @dbname + '

declare @inx_ret_value int ,@int1 int
select  @inx_ret_value = 1

exec @inx_ret_value = sp_check_removable ' + @char_autofix + '

--Use @@rowcount for a user_assignable global variable for communication.
if @inx_ret_value <> 0	--bad!!!!
	begin
	select @int1 = 1
	return
	end
else
	begin
	select @int1 = 1 where 1=2
	return
	end
'
)

if @@rowcount > 0
        return (1)  --Error was returned by other proc, so exit


-- Take it offline
raiserror('' ,0,1)
exec sp_dboption @dbname,'offline','true'

return(0)
go


print ' '
raiserror(15339,-1,-1,'MS_sqlctrs_users')
go
create procedure MS_sqlctrs_users --- 1996/08/30 21:49
as
select
		 lg.loginname + ' - ' + convert(varchar(30),pr.spid)
		,pr.memusage		as 'Memory (8K Pages)' -- 2K in 6.5
		,pr.cpu			as 'CPU time'
		,pr.physical_io
		,count(lk.spid)		as 'Locks held'
		,pr.spid
	from
		 master.dbo.sysprocesses	pr	left outer join
		 master.dbo.syslocks	lk	on pr.spid=lk.spid
		,master.dbo.syslogins	lg
	where
		 pr.sid	= lg.sid
	group by
		 lg.loginname
		,pr.spid
		,pr.memusage
		,pr.cpu
		,pr.physical_io
go

print ' '
raiserror(15339,-1,-1,'sp_autostats')
go
CREATE PROCEDURE sp_autostats
	@tblname 	nvarchar(776),
	@flagc		varchar(10)=null,
	@indname	sysname=null
AS
BEGIN
	DECLARE @flag bit, @nrc_mask int

	/*
	**  Check flag
	*/
	SET @flag = CASE lower(@flagc)
		WHEN 'on' 	THEN 1
		WHEN 'off' 	THEN 0
		ELSE NULL
		END

	IF @flag IS NULL AND @flagc IS NOT NULL
    	BEGIN
		RAISERROR(17000,-1,-1)
        	RETURN (1)
    	END

	/*
	** Set NORECOMPUTE mask
	*/
	SET @nrc_mask = 16777216

	/*
	** Check we are executing in the correct database
	*/
	DECLARE @db sysname
	SELECT @db = parsename(@tblname, 3)

	IF (@db IS NOT NULL AND @db <> db_name())
	BEGIN
		RAISERROR(15387,-1,-1)
		RETURN (1)
	END

	/*
	** PRINT or UPDATE status?
	*/
	IF (@flag IS NULL)
	BEGIN

		-- Display global settings (sp_dboption)
		--
		PRINT 'Global statistics settings for ' + quotename(db_name(), '[') + ':'
		PRINT '  Automatic update statistics: ' + (CASE WHEN DatabaseProperty(db_name(), 'IsAutoUpdateStatistics') = 1 THEN 'ON' ELSE 'OFF' END)
		PRINT '  Automatic create statistics: ' + (CASE WHEN DatabaseProperty(db_name(), 'IsAutoCreateStatistics') = 1 THEN 'ON' ELSE 'OFF' END)
		PRINT ''

		-- Display the current status of the index(s)
		--
		PRINT 'Settings for table ' + quotename(@tblname, '[')
		PRINT ''
		SELECT 'Index Name' = quotename(si.name, '['),
		       'AUTOSTATS' =
			CASE (si.status & @nrc_mask)
				WHEN @nrc_mask THEN 'OFF'
				ELSE 'ON'
			END,
		       'Last Updated' = stats_date(object_id(@tblname), si.indid)
		FROM sysindexes si
		WHERE si.id = object_id(@tblname) AND		-- Table
		      si.indid BETWEEN 1 AND 254 AND		-- Skip HEAP/TEXT index
			CASE 					-- Match name
				WHEN @indname IS NULL THEN 1
				WHEN @indname = si.name THEN 1
				ELSE 0
			END = 1
	END
	ELSE
	BEGIN
		DECLARE @_rowcount int,
				@tabid int,
				@objtype varchar(2)

		-- VERIFY WE HAVE A USER-TABLE/INDEXED-VIEW BY THIS NAME IN THE DATABASE
		SELECT @tabid = id, @objtype = xtype FROM sysobjects
			WHERE id = OBJECT_ID(@tblname, 'local') AND (xtype = 'U' OR xtype = 'V')
		IF (@tabid IS NULL OR
				(@objtype = 'V' AND
				(ObjectProperty(@tabid, 'IsIndexed') = 0 OR
				ObjectProperty(@tabid, 'IsMSShipped') = 1))
			)
		begin
			raiserror(15390,-1,-1,@tblname)
			RETURN @@ERROR
		end

		BEGIN TRANSACTION upd_tran

		-- Lock the table schema and check permissions
		--
		DBCC LOCKOBJECTSCHEMA(@tblname)

		-- Flip the status bits
		--
		DECLARE @batch varchar(8000)

		UPDATE sysindexes
		SET status =
			CASE @flag
			WHEN 1 THEN status &~ @nrc_mask
			ELSE status | @nrc_mask
			END
		WHERE id = object_id(@tblname) AND		-- Table
		      indid <> 255 AND				-- Skip TEXT index
		      CASE 					-- Match name
			WHEN @indname IS NULL THEN 1
			WHEN @indname = name THEN 1
			ELSE 0
		      END = 1

		-- Save the affected rowcount
		SET @_rowcount = @@rowcount

		COMMIT TRANSACTION upd_tran

		-- Show the user how many indices were affected
		PRINT 'Automatic statistics maintenance turned ' +
			CASE @flag WHEN 1 THEN 'ON' ELSE 'OFF' END +
			' for ' + convert(varchar(5), @_rowcount) +
			' indices.'

	END

	-- All done
	--
	RETURN(0) -- sp_autostats
END
GO

raiserror(15339,-1,-1,'sp_updatestats')
go
-- required for sp_updatestats so it can update stats on on ICC/IVs
set ansi_nulls on
go
CREATE PROCEDURE sp_updatestats
@resample CHAR(8)='NO'
AS

	DECLARE @dbsid varbinary(85)

	SELECT  @dbsid = sid
	FROM master.dbo.sysdatabases
    WHERE name = db_name()

	/*Check the user sysadmin*/
	 IF NOT is_srvrolemember('sysadmin') = 1 AND suser_sid() <> @dbsid
		BEGIN
			RAISERROR(15247,-1,-1)
			RETURN (1)
		END

	if UPPER(@resample)<>'RESAMPLE' AND UPPER(@resample)<>'NO'
	begin
   		raiserror(N'Invalid option: %s', 1, 1, @resample)
		return (1)
	end

	-- required so it can update stats on on ICC/IVs
	set ansi_nulls on
	set quoted_identifier on
	set ansi_warnings on
	set ansi_padding on
	set arithabort on
	set concat_null_yields_null on
	set numeric_roundabort off



	DECLARE @exec_stmt nvarchar(540)
	DECLARE @tablename sysname
	DECLARE @uid smallint
	DECLARE @user_name sysname
	DECLARE @tablename_header varchar(267)
	DECLARE ms_crs_tnames CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT name, uid FROM sysobjects WHERE type = 'U'
	OPEN ms_crs_tnames
	FETCH NEXT FROM ms_crs_tnames INTO @tablename, @uid
	WHILE (@@fetch_status <> -1)
	BEGIN
		IF (@@fetch_status <> -2)
		BEGIN
			SELECT @user_name = user_name(@uid)
			SELECT @tablename_header = 'Updating ' + @user_name +'.'+ RTRIM(@tablename)
			PRINT @tablename_header
			SELECT @exec_stmt = 'UPDATE STATISTICS ' + quotename( @user_name , '[')+'.' + quotename( @tablename, '[') 
			if (UPPER(@resample)='RESAMPLE') SET @exec_stmt = @exec_stmt + ' WITH RESAMPLE'
			EXEC (@exec_stmt)
		END
		FETCH NEXT FROM ms_crs_tnames INTO @tablename, @uid
	END
	PRINT ' '
	PRINT ' '
	raiserror(15005,-1,-1)
	DEALLOCATE ms_crs_tnames
	RETURN(0) -- sp_updatestats
go
-- was only required for sp_updatestats
set ansi_nulls off
go

-------------------------------- sp_createstats --------begin---------------------------
raiserror(15339,-1,-1,'sp_createstats')
go
-- required for sp_createstats so it can update stats on on ICC/IVs
set ansi_nulls on
go

CREATE PROCEDURE sp_createstats
@indexonly CHAR(9)= 'NO',     -- Optional 'INDEXONLY' text - if present, then only the columns
				-- covered by indexes are subject of statistics creation
@fullscan  CHAR(9)= 'NO',      -- Optional 'FULLSCAN' text - if present, then the statistics
				-- will be updated with full scan rather than sampling
@norecompute  CHAR(12)= 'NO'      -- Optional 'NORECOMPUTE' text - if present, then statistics
				-- will not be updated automatically
AS
/*
	NOTE: This sp will update statistics for *all* columns of all tables
	which the user has the privilege to update stats on (sysadmin, dbo, owner).
	The following columns are not considered
	- first column of an index
	- column which already has statistics
	- unelligible columns (Text and image columns consisting of ntext, text, or image data type,
	  bit, and computed columns)

*/

declare @sysadmin int
	,@dbname sysname

-- remember dbname

  	SELECT @dbname = db_name()

-- create temporary table (column, index position)
create table #colpostab
(	col_name  sysname collate database_default ,
	col_pos	  int,
)

set nocount on

-- required for sp_createstats so it can update stats on on ICC/IVs
set ansi_nulls on
set quoted_identifier on
set ansi_warnings on
set ansi_padding on
set arithabort on
set concat_null_yields_null on
set numeric_roundabort off

DECLARE @exec_stmt nvarchar(540)
DECLARE @tablename sysname
DECLARE @columnname sysname
--DECLARE @shortcolumnname sysname
DECLARE @indexname sysname
DECLARE @uid smallint
DECLARE @indid smallint
DECLARE @position smallint
DECLARE @table_id  int
DECLARE @user_name sysname
DECLARE @numcols int   -- number of eligible columns found
DECLARE @msg nvarchar(386)
DECLARE @timestamp varchar(17)

DECLARE @tablename_header varchar(267)

DECLARE ms_crs_tnames CURSOR LOCAL STATIC FOR
SELECT name, id, uid FROM sysobjects WHERE type = 'U' and ((object_id('[#colpostab]') is NULL) OR (id <> object_id('[#colpostab]')))

SELECT @numcols = 0

OPEN ms_crs_tnames
FETCH NEXT FROM ms_crs_tnames INTO @tablename, @table_id, @uid
WHILE (@@fetch_status <> -1)
BEGIN
	IF ((@@fetch_status <> -2) AND  (is_member('db_owner')=1) OR (is_member('ddl_admin')=1) OR (is_member(user_name(@uid))=1) OR (user_id() = @uid))
	BEGIN
		-- these are all columns for which the statistics will be updated
		DECLARE ms_crs_cnames CURSOR LOCAL FOR SELECT c.name   FROM syscolumns c, systypes t
			WHERE c.id = @table_id AND c.xtype = t.xusertype AND
				 (t.name NOT IN ('text', 'ntext', 'image', 'timestamp','bit'))
			AND ((c.colstat & 0x0004) <> 0x0004)
			AND (c.length<=900)
			AND c.name NOT IN (SELECT col_name FROM #colpostab WHERE col_pos = 1)
			AND ((c.name IN (SELECT col_name FROM #colpostab)) OR (@indexonly <> 'indexonly'))

		-- populate temporary table of all (column, index position) tuples for this table

		TRUNCATE TABLE #colpostab

		-- for each index on the table, loop though all columns and insert rows
		-- OPEN CURSOR OVER INDEXES
		DECLARE ms_crs_ind CURSOR LOCAL STATIC FOR
			SELECT indid, name FROM sysindexes
				where id = @table_id and indid > 0 and indid < 255 order by indid

		OPEN ms_crs_ind
		FETCH ms_crs_ind into @indid , @indexname

		-- IF AN INDEX EXISTS

		WHILE @@fetch_status >= 0
		BEGIN
			-- Every index has at least one column at position 1
			INSERT INTO #colpostab VALUES (index_col(@tablename,@indid,1),1)
			-- now try position 2 and beyond....
			SELECT @columnname = index_col(@tablename, @indid, 2)
			SELECT @position = 2
			WHILE (@columnname is not null )
				BEGIN
					INSERT INTO #colpostab VALUES (@columnname,@position)
					SELECT @position = @position +1
					SELECT @columnname = index_col(@tablename, @indid, @position)
				END
			-- Next Index
			FETCH ms_crs_ind into @indid , @indexname

		END
		CLOSE ms_crs_ind
		DEALLOCATE ms_crs_ind

		-- now go over all columns which are eligible for updating statistics
		-- and are not first columns of any index
		-- optionaly we test if they are covered by some index (as non-leading)

		SELECT @user_name = user_name(@uid)

		OPEN ms_crs_cnames

		FETCH NEXT FROM ms_crs_cnames INTO @columnname
		IF @@fetch_status < 0
		BEGIN
			select @msg = @dbname +'.'+ @user_name +'.'+ @tablename
			raiserror(15013,-1,-1,@msg)
		END
		ELSE
		BEGIN
			select @msg = @dbname +'.'+ @user_name +'.'+ @tablename
			raiserror(15018, -1, -1, @msg)
		END

		WHILE @@fetch_status >= 0
		BEGIN
		 	 SELECT @numcols = @numcols +1
			 -- use the column name as the name for the statistics as well
			 select @exec_stmt = 'CREATE STATISTICS ' +  quotename(@columnname, '[')  + ' ON ' +
		 quotename( @user_name ,'[')+'.' + quotename( @tablename, '[')+'('+ quotename( @columnname, '[')+')'
			-- determining the correct suffix
			if ((@fullscan = 'FULLSCAN') AND (@norecompute = 'NORECOMPUTE'))
				select @exec_stmt = @exec_stmt + ' WITH FULLSCAN, NORECOMPUTE'
			else if (@fullscan = 'FULLSCAN') select @exec_stmt = @exec_stmt + ' WITH FULLSCAN'
			else if (@norecompute = 'NORECOMPUTE') select @exec_stmt = @exec_stmt + ' WITH NORECOMPUTE'
			EXEC (@exec_stmt)
			--PRINT 'Statement='+@exec_stmt
			if (@@ERROR = 0)  -- otherwise the CREATE STATS will give a message
			     PRINT '     ' + @columnname
			FETCH NEXT FROM ms_crs_cnames INTO @columnname
		END
		CLOSE ms_crs_cnames
		DEALLOCATE ms_crs_cnames
	END
	FETCH NEXT FROM ms_crs_tnames INTO @tablename, @table_id, @uid
END

PRINT ' '
raiserror(15020,-1,-1,@numcols)

DEALLOCATE ms_crs_tnames

IF (object_id('[#colpostab]') is not null)
   begin
            drop table [#colpostab]
   end

return(0) -- sp_createstats
go
-- was only required for sp_createstats
set ansi_nulls off
go

------------------------------- sp_cycle_errorlog -----------------------------------
raiserror(15339,-1,-1,'sp_cycle_errorlog')
go
create procedure sp_cycle_errorlog  --- 1997/06/24
as
if (not (is_srvrolemember('sysadmin') = 1))  -- Make sure that it is the SA executing this.
        begin
                raiserror(15247,-1,-1)
                return(1)
        end

dbcc errorlog
return (0)
go





-------------------------------- sp_helptrigger -----------------------------------

raiserror(15339,-1,-1,'sp_helptrigger')
go
create procedure sp_helptrigger  --- 1997/06/24
    @tabname nvarchar(776),			/*	Table name		*/
	@triggertype char(6) = NULL	/*	Trigger type	*/
as

declare @objid int,        /* id of the object */
		@dbname sysname,
		@deltrig int,
		@instrig int,
		@updtrig int

-- Check to see that the object names are local to the current database.
select @dbname = parsename(@tabname,3)

if @dbname is not null and @dbname <> db_name()
begin
		raiserror(15250,-1,-1)
		return (1)
end

select @objid =  id from sysobjects where id = object_id(@tabname)
	and type in ('S','U', 'V')

if @objid is null
	begin
		select @dbname = db_name()
		raiserror(15009,-1,-1,@tabname,@dbname)
		return(1)
	end

/*	Check that input type is UPDATE, INSERT, DELETE	*/
if @triggertype  is not null and not UPPER(@triggertype ) in ('UPDATE', 'INSERT', 'DELETE')
      begin
         raiserror(15305,-1,-1)
         return(1)
      end

if @triggertype  is NULL
	select
	trigger_name = name,
	trigger_owner = user_name(uid),
	isupdate = ObjectProperty( id, 'ExecIsUpdateTrigger'),
	isdelete = ObjectProperty( id, 'ExecIsDeleteTrigger'),
	isinsert = ObjectProperty( id, 'ExecIsInsertTrigger'),
	isafter = ObjectProperty( id, 'ExecIsAfterTrigger'),
	isinsteadof = ObjectProperty( id, 'ExecIsInsteadOfTrigger')
	from sysobjects
	where parent_obj = @objid and type = 'TR'
else
begin
	set @deltrig = case
		when  upper(@triggertype ) = 'DELETE' then 0
		else -1 end
	set @instrig = case
		when  upper(@triggertype ) = 'INSERT' then 0
		else  -1 end
	set @updtrig = case
		when  upper(@triggertype ) = 'UPDATE' then 0
		else -1 end
	select
	trigger_name = name,
	trigger_owner = user_name(uid),
	isupdate = ObjectProperty( id, 'ExecIsUpdateTrigger'),
	isdelete = ObjectProperty( id, 'ExecIsDeleteTrigger'),
	isinsert = ObjectProperty( id, 'ExecIsInsertTrigger'),
	isafter = ObjectProperty( id, 'ExecIsAfterTrigger'),
	isinsteadof = ObjectProperty( id, 'ExecIsInsteadOfTrigger')
	from sysobjects
	where parent_obj = @objid and
	ObjectProperty( id, 'ExecIsDeleteTrigger') > @deltrig and
	ObjectProperty( id, 'ExecIsInsertTrigger') > @instrig and
	ObjectProperty( id, 'ExecIsUpdateTrigger') > @updtrig and
	type = 'TR'
end
return(0)  --sp_helptrigger
go

raiserror(15339,-1,-1,'sp_fixindex')
go
create procedure sp_fixindex
		@dbname		sysname,
		@tabname	sysname,  				/* system table name */
		@indid		int						/* index id value    */
as

	declare @indexname sysname
	/*
	**	Description:	allow the SA to force a drop and then a
	**			create index on system catalogs.
	**
	**	Usage:		sp_fixindex  database, systemcatalog, ind_id
	**
	**	Note:		before using this procedure the database has to
	**			be in single user mode. The sp_dboption has to
	**			be used for user databases, and update of
	**			sysdatabases for master.
	**
	*/

	/* Check that current db is db for processing*/
	if db_name() <> @dbname
		begin
			raiserror(15555,-1,-1, @dbname)
			return(1)
		end

	/*
	**	Make sure we are 'fixing' a system catalog.
	*/
	if not exists (select name from sysobjects where name = @tabname and type = 'S')
		begin
			raiserror(15193,-1,-1)
			return (1)
		end


	/* Check database is in single user mode */
	if ((select status from master..sysdatabases where name = @dbname) &
	   (select number from master..spt_values where name = 'single user' and  type = 'D') = 0) and
	   (select value from master..syscurconfigs where config = 102) <> 1
		begin
			raiserror(15308,-1,-1, @dbname)
			return(1)
		end

	/*
	**	Make sure that we are doing this on somenthing that
	**	has indexes (or real tables).
	*/
	if exists (select id from sysindexes where id = object_id(@tabname) and status & 8 <> 0)
		begin
			raiserror(15194,-1,-1)
			return (1)
		end

	/* Get the index name	*/
	select @indexname = name from sysindexes where id = object_id(@tabname) and  indid = @indid

	if @indexname is null
		begin
			raiserror(15323,-1,-1, @tabname)
			return (1)
		end

    if (object_id(@tabname) <= 100)
        begin
            dbcc dbrepair(@dbname, repairindex, @tabname, @indid)
        end
    else
        begin
	        dbcc dbreindex(@tabname, @indexname)
        end

	return (0)
go

------------------------------- sp_settriggerorder ---------------------------------

if object_id('sp_settriggerorder','P') IS NOT NULL
	drop procedure sp_settriggerorder
go
raiserror(15339,-1,-1,'sp_settriggerorder')
go
create procedure sp_settriggerorder
	@triggername	nvarchar(517),	-- name of the trigger (may be 2-part)
	@order			varchar(10),	-- first, last, or none
	@stmttype		varchar(10)		-- insert, update, or delete
as
	set nocount on
	declare @firstbit		int		-- bit for first-trigger of given @stmttype
			,@lastbit		int		-- bit for last-trigger of given @stmttype
			,@setbit		int		-- status bit to set (0 for clear) based on @stmttype/@order
			,@trigid		int		-- objid of the trigger
			,@tableid		int		-- objid of the trigger's table
			,@uid			smallint --user id
			,@tabname		nvarchar(517)	-- name of the trigger's table

	-- VALIDATE PARAMETERS and obtain bits affected --
	SELECT @order = rtrim(@order), @stmttype = rtrim(@stmttype)
	SELECT @firstbit = case lower(@stmttype)
				when 'delete' then 16384
				when 'update' then 65536
				when 'insert' then 262144
				else NULL end
	SELECT @lastbit = @firstbit * 2		-- NOTE DEPENDENCY ON BIT LAYOUT HERE!
	SELECT @setbit = case lower(@order)
				when 'none' then 0
				when 'first' then @firstbit
				when 'last' then @lastbit
				else NULL end
	IF @setbit is NULL OR @firstbit IS NULL
	begin
		raiserror(15600,-1,-1, 'sp_settriggerorder')
		return (1)
	end

	-- BEGIN TRAN AND LOCK SCHEMA (also checks permissions) --
	BEGIN TRAN
	DBCC LOCKOBJECTSCHEMA(@triggername)
	if @@error <> 0
		goto abort_exit

	-- VERIFY PROPER OBJECT TYPE --
	select @trigid = object_id(@triggername, 'local')
	select @tableid = parent_obj, @uid = uid from sysobjects where id = @trigid AND xtype='TR'
		and ObjectProperty(@trigid,'ExecIsInsteadofTrigger')=0
		and ObjectProperty(@trigid,'ExecIs'+@stmttype+'Trigger')=1
	if (@tableid is NULL)
	begin
		if ObjectProperty(@trigid,'ExecIs'+@stmttype+'Trigger')=0
			raiserror(15125,-1,-1, @triggername, @stmttype)
		else if ObjectProperty(@trigid,'ExecIsInsteadofTrigger')=1
			raiserror(15133, -1, -1, @triggername)
		else
			raiserror(15126,-1,-1,@triggername)
		goto abort_exit
	end

	-- LOCK THE TABLE SCHEMA TOO --
	select @tabname = quotename(user_name(@uid))+'.'+quotename(object_name(@tableid))
	DBCC LOCKOBJECTSCHEMA(@tabname)
	if @@error <> 0
		goto abort_exit

	-- VERIFY FIRST/LAST OF GIVEN TYPE DOESN'T ALREADY EXIST --
	IF EXISTS (select * from sysobjects where parent_obj = @tableid AND xtype='TR' AND id <> @trigid
			 AND ObjectProperty(id, 'ExecIs'+@order+@stmttype+'Trigger') = 1)
	BEGIN
		raiserror(15130,-1,-1,@tabname, @order, @stmttype)
		goto abort_exit
	END

	-- SET THE ORDER AS REQUESTED, COMMIT & RETURN SUCCESS --
	update sysobjects set status = ((status&~(@firstbit|@lastbit))|@setbit) where id = @trigid
	commit transaction
	return(0)

	-- ROLLBACK TRAN & EXIT-FAIL --
abort_exit:
	rollback transaction
	return(1)
go

grant execute on sp_settriggerorder to public
go


/**********************EXTENDED PROPERTY SPS**********************************/
raiserror(15339,-1,-1,'system_function_schema.fn_listextendedproperty')
go
create function system_function_schema.fn_listextendedproperty
	(@name sysname				= NULL,
	@level0type	varchar(128)	= NULL,
	@level0name	sysname			= NULL,
	@level1type	varchar(128)	= NULL,
	@level1name	sysname			= NULL,
	@level2type	varchar(128)	= NULL,
	@level2name	sysname			= NULL
)

returns @tab table(objtype varchar(128) null,
		objname sysname null,
		name sysname not null,
		value sql_variant null)
as
begin

declare @objtype varchar(2),
		@colnum smallint


select	@level0type	= UPPER(@level0type)
		,@level1type	= UPPER(@level1type)
		,@level2type	= UPPER(@level2type)

if @level0type is null and @level0name is null
begin
	if	@level1type is null and @level1name is null and
		@level2type is null and @level2name is null
	begin
		insert @tab
		select NULL,NULL, name, value
		from sysproperties
		where	type = 0 and is_member('db_owner') = 1 and
			(@name is null or @name = name)
	end

	return
end

if	@level1type is null and @level1name is null and
	@level2type is null and @level2name is null
begin
	if @level0type = 'TYPE'
	begin
		insert @tab
		select @level0type, t.name, p.name, p.value
		from sysproperties p, systypes t
		where	p.type = 1 and p.id = 0 and p.smallid = xusertype and
			(@level0name is null or t.name = @level0name) and
			(@name is null or @name = p.name)
	end
	else if @level0type = 'USER'
	begin
		insert @tab
		select @level0type, u.name, p.name, p.value
		from sysusers u, sysproperties p
		where p.type = 2 and p.id = 0 and p.smallid = uid and
			(is_member('db_owner') = 1 or is_member(u.name) = 1) and
			(@level0name is null or u.name = @level0name) and
			(@name is null or @name = p.name)
	end
	return
end
if @level0type is null or @level0name is null or @level0type <> 'USER'
	return

if @level2type is null and @level2name is null
begin
	if @level1type in ('TABLE', 'VIEW', 'PROCEDURE', 'RULE', 'DEFAULT')
	begin
		select @objtype = case @level1type
					when 'TABLE' then 'U'
					when 'VIEW' then 'V'
					when 'PROCEDURE' then 'P'
					when 'RULE' then 'R'
					when 'DEFAULT' then 'D'
					end

		insert @tab
		select @level1type, o.name, p.name, p.value
		from sysobjects o, sysproperties p
		where	p.type = 3 and p.smallid = 0 and o.id = p.id and
				(permissions(o.id) > 0 or @objtype in ('R', 'D')) and
				o.uid = user_id(@level0name) and
				o.xtype = @objtype and o.parent_obj = 0 and
				(@level1name is null or o.name = @level1name) and
				(@name is null or @name = p.name)

	end
	else if (@level1type = 'FUNCTION')
	begin
			insert @tab
			select @level1type, o.name, p.name, p.value
			from sysobjects o, sysproperties p
			where	p.type = 3 and p.smallid = 0 and o.id = p.id and
					permissions(o.id) > 0 and
					o.uid = user_id(@level0name) and
					o.xtype in ('TF','FN','IF') and
					(@level1name is null or o.name = @level1name) and
					(@name is null or @name = p.name)
	end

	return
end
if @level1type is null or @level1name is null
	return

if @level2type = 'COLUMN'
begin
	if @level1type not in ('TABLE', 'VIEW', 'FUNCTION')
		return

	insert @tab
	select @level2type, c.name, p.name, p.value
	from syscolumns c, sysproperties p, sysobjects o
	where	p.type = 4 and p.smallid = c.colid and
			c.id = object_id(quotename(@level0name)+'.'+quotename(@level1name)) and
			c.id = p.id and permissions(c.id,c.name) > 0 and c.number = 0 and
			(@level2name is null or c.name = @level2name) and
			(@name is null or @name = p.name) and
			c.id = o.id and o.xtype in ('U','V','TF','IF')

end
else if @level2type ='TRIGGER'
begin
	if @level1type not in ('TABLE', 'VIEW')
		return

	insert @tab
	select @level2type, o.name, p.name, p.value
		from sysobjects o, sysproperties p
		where	p.type = 3 and p.smallid = 0 and p.id = o.id and
			o.parent_obj = object_id(quotename(@level0name)+'.'+quotename(@level1name)) and
			permissions(o.parent_obj) > 0 and
			o.xtype = 'TR' and
			(@level2name is null or o.name = @level2name) and
			(@name is null or @name = p.name)

end
else if @level2type = 'CONSTRAINT'
begin
	if @level1type not in ('TABLE', 'FUNCTION')
		return

	insert @tab
	select @level2type, o.name, p.name, p.value
		from sysobjects o, sysproperties p
		where	p.type = 3 and p.smallid = 0 and p.id = o.id and
			o.parent_obj = object_id(quotename(@level0name)+'.'+quotename(@level1name)) and
			permissions(o.parent_obj) > 0 and
			o.xtype in ('C','D','F','PK','UQ') and
			(@level2name is null or o.name = @level2name) and
			(@name is null or @name = p.name)

end
else if @level2type = 'INDEX'
begin
	if @level1type not in ('TABLE', 'VIEW')
		return

	insert @tab
	select @level2type, i.name, p.name, p.value
		from sysindexes i, sysproperties p
		where	p.type = 6 and permissions(i.id) > 0 and
			p.id = object_id(quotename(@level0name)+'.'+quotename(@level1name)) and
			p.smallid = i.indid and i.id = p.id and
			(@level2name is null or i.name = @level2name) and
			(@name is null or @name = p.name) and
			i.indid not in (0,255) and i.status&0x1800 = 0	-- no PK/UQ constraint
end
else if @level2type = 'PARAMETER'
begin
	if @level1type not in ('PROCEDURE', 'FUNCTION')
		return

	select @objtype = xtype from sysobjects where id = object_id(quotename(@level0name)+'.'+quotename(@level1name))
	select @colnum = case  @objtype when 'FN' then 0 else 1 end

	insert @tab
	select @level2type, c.name, p.name, p.value
	from syscolumns c, sysproperties p
	where	p.type = 5 and p.smallid = c.colid and
			c.id = object_id(quotename(@level0name)+'.'+quotename(@level1name)) and
			c.id = p.id and permissions(c.id) > 0 and c.number = @colnum and
			(@level2name is null or c.name = @level2name) and
			(@name is null or @name = p.name) and
			@objtype in ('P', 'TF','FN','IF')
end

return
end
go

raiserror(15339,-1,-1,'sp_validatepropertyinputs')
go
create procedure sp_validatepropertyinputs
	@name			sysname
	,@level0type		varchar(128)
	,@level0name		sysname
	,@level1type		varchar(128)
	,@level1name		sysname
	,@level2type		varchar(128)
	,@level2name		sysname
	,@id				int				OUTPUT
	,@smallid			smallint		OUTPUT
	,@type				tinyint			OUTPUT
	,@exists			int				OUTPUT
	,@fullname			nvarchar(400)	OUTPUT
	,@objname			nvarchar(517)	OUTPUT		-- level 1 object name

as
-----------------------------------------------------
-- NOTE: FOR INTERNAL USE ONLY (sp_addextendedproperty,sp_updateextendedproperty,sp_dropextendedproperty)
--      DO NOT DOCUMENT OR USE!
-----------------------------------------------------

	declare @ret int

	select @id = 0, @smallid = 0	--Initialize 0 is used instead of null

	Select	@level0type		= UPPER(@level0type)
			,@level1type	= UPPER(@level1type)
			,@level2type	= UPPER(@level2type)

	declare @invalidlevel varchar(25)
			,@objtype varchar(2)
			,@uid int
			,@objid int
			,@lev2objexists tinyint

	if @name is null
		return (2)	--return to calling proc which will raiserror

	execute @ret = sp_validname @name
	if (@ret <> 0)
        return (1)


	if (@level2type is not null and
		(@level1type is null or @level0type is null)) or
		(@level1type is not null and @level0type is null)
			return (2)	--return to called proc which will raiserror

	select @fullname =
		case
			when (@level2name is not null) then
				@level0name + '.' + @level1name + '.' + @level2name
 			when (@level1name is not null) then
				@level0name + '.' + @level1name
			when (@level0name is not null) then
				@level0name
		end
	select @fullname = isnull(@fullname,'object specified')

	if @level0type is null and @level0name is null
	begin

		--must be dbo 
		if is_member('db_owner') = 0
		begin
			raiserror(15247,-1,-1)
			return (1)
		end

		--Database Property
		select	@type = 0 --Indicates database entry

	end
	else if @level0type = 'TYPE' and @level0name is not null
	begin

		select	@smallid = xusertype,@type = 1
		from	systypes
		where	name = @level0name and xusertype > 256 --only udtypes

	end
	else if @level0type = 'USER' and @level0name is not null
	begin

		select	@type = 2, @smallid = uid
		from	sysusers
		where	name = @level0name and
				(issqluser = 1 or isntname = 1 or @level1type is not null) and --no alias/should only be users (any owner ok for objects)
				uid NOT IN (3,4) --no INFORMATION_SCHEMA, system_function_schema

	end
	else
		return (2)	--return to called proc which will raiserror


	--Check is user/type does not exits then @type will be null
	if @type is null or
	(@level1type is null and @smallid in (1,2)) --Not permitted to add prop to dbo/guest
	begin
		raiserror(15135,-1,-1,@fullname)
		return (1)
	end

	if @level1type in ('TABLE', 'VIEW', 'PROCEDURE', 'RULE', 'DEFAULT', 'FUNCTION')
			and @level1name is not null
	begin

		if not (@level0type = 'USER')
			return (2)	--return to called proc which will raiserror

		--Not for temp tables
		if substring(@level1name,1,1) = N'#'
		begin
			raiserror(15135,-1,-1,@fullname)
			return (1)
		end

		select @uid = @smallid
		select @smallid = 0

		if (@level1type = 'FUNCTION')
		begin
			select	@id = id,@type = 3, @objtype = xtype
			from	sysobjects
			where	name = @level1name and uid = @uid and
					xtype in ('FN','TF','IF') and parent_obj = 0
		end
		else
		begin
		--Can only be for objects and not default/rule constraints
			select @objtype = case @level1type
						when 'TABLE' then 'U'
						when 'VIEW' then 'V'
						when 'PROCEDURE' then 'P'
						when 'RULE' then 'R'
						when 'DEFAULT' then 'D'
					end

			select	@id = id,@type = 3
			from	sysobjects
			where	name = @level1name and uid = @uid and
					xtype = @objtype and parent_obj = 0
		end
		--if object not found than @id will = 0
		if @id = 0
		begin
			raiserror(15135,-1,-1, @fullname)
			return (1)
		end

		select @objname = QUOTENAME(@level0name) + '.' + QUOTENAME(@level1name)

	end
	else if not (@level1type is null and @level1name is null)
		return (2)	--return to called proc which will raiserror

	--Check Permissions
	if @id <> 0 and
		is_member('db_owner') = 0 and is_member(@level0name) = 0
		and is_member('db_ddladmin') = 0
	begin
		raiserror(15247,-1,-1)
		return (1)
	end
	else if @smallid <> 0 and is_member('db_owner') = 0 and
		(@level0type = 'USER') OR
		-- For type: may also be db_ddladmin or an owner
		(@level0type = 'TYPE' and is_member('db_ddladmin')=0 and
			is_member(user_name((select uid from systypes where xusertype=@smallid)))=0)
	begin
		raiserror(15247,-1,-1)
		return (1)
	end

	if @level2type = 'COLUMN'  and @level2name is not null
	begin

		if @level1type not in ('TABLE','VIEW','FUNCTION')
			return (2)	--return to called proc which will raiserror

		select	@smallid = colid, @type = 4,@lev2objexists = 1
		from	syscolumns
		where	id = @id and name = @level2name and
				number = 0	--specified columns entry

	end
	else if @level2type = 'PARAMETER' and @level2name is not null
	begin

		if @level1type not in ('PROCEDURE','FUNCTION')
			return (2)	--return to called proc which will raiserror

		--scalar function params have number as 0 for params
		if @objtype = 'FN'
			select	@smallid = colid, @type = 5,@lev2objexists = 1
			from	syscolumns
			where	id = @id and name = @level2name and
					number = 0	--specified parameter entry

		else
			select	@smallid = colid, @type = 5,@lev2objexists = 1
			from	syscolumns
			where	id = @id and name = @level2name and
					number = 1	--specified parameter entry

	end
	else if @level2type ='TRIGGER' and @level2name is not null
	begin

		if @level1type not in ('TABLE','VIEW')
			return (2)	--return to called proc which will raiserror

		select @objid = @id
		select @id = 0

		select	@id = id, @type = 3, @lev2objexists = 1
		from	sysobjects
		where	name = @level2name and parent_obj = @objid and
				xtype = N'TR'

	end
	else if @level2type = 'CONSTRAINT' and @level2name is not null
	begin

		if not @level1type in ('TABLE','FUNCTION')
			return (2)	--return to called proc which will raiserror

		select @objid = @id
		select @id = 0

		select	@id = id, @type = 3, @lev2objexists = 1
		from	sysobjects
		where	name = @level2name and parent_obj = @objid and
				xtype in ('C','D','F', 'PK', 'UQ')

	end
	else if @level2type = 'INDEX' and @level2name is not null
	begin

		if not @level1type in ('TABLE', 'VIEW')
			return (2)	--return to called proc which will raiserror

		select	@smallid = indid, @type = 6, @lev2objexists = 1
		from	sysindexes
		where	name = @level2name and id = @id and
				indid not in (0,255) and status&0x1800 = 0 --no PK/U constraints

	end
	else if not (@level2type is null and @level2name is null)
			return (2)	--return to called proc which will raiserror

	--Does the level2 obj exists
	if @lev2objexists is null and @level2type is not null
	begin
		raiserror(15135,-1,-1, @fullname)
		return (1)
	end

	--Check if property exists
	select @exists = (select count(*) from sysproperties
					where	@type = type and
						@id =	id	and
						@smallid =	smallid	and
						@name = name)

	return (0)
go

raiserror(15339,-1,-1,'sp_addextendedproperty')
go
create procedure sp_addextendedproperty
	@name sysname,
	@value sql_variant			= NULL,
	@level0type	varchar(128)	= NULL,
	@level0name	sysname			= NULL,
	@level1type	varchar(128)	= NULL,
	@level1name	sysname			= NULL,
	@level2type	varchar(128)	= NULL,
	@level2name	sysname			= NULL
as

	declare @id int
		,@smallid smallint
		,@type tinyint
		,@ret int
		,@exists int
		,@fullname nvarchar(400)
		,@objname nvarchar(517)

		if datalength(@value) > 7500
		begin
			raiserror(15097,-1,-1)
			return 1
		end

		execute @ret = sp_validatepropertyinputs
						@name
						,@level0type
						,@level0name
						,@level1type
						,@level1name
						,@level2type
						,@level2name
						,@id			OUTPUT
						,@smallid		OUTPUT
						,@type			OUTPUT
						,@exists		OUTPUT
						,@fullname		OUTPUT
						,@objname		OUTPUT

	if @ret = 2
		raiserror(15600,-1,-1,'sp_addextendedproperty')
	if @ret <> 0
		return (1)

	if ( @exists = 1 ) --Indicates property for object does exist
	begin
		raiserror(15233,-1,-1,@name, @fullname)
		return(1)
	end

	BEGIN TRANSACTION

		if @objname is not null
			DBCC LockObjectSchema(@objname)

		insert into sysproperties (type, id, smallid, name, value)
		values (@type, @id, @smallid, @name, @value)

	COMMIT TRANSACTION

	return(0)
go


raiserror(15339,-1,-1,'sp_updateextendedproperty')
go
create procedure sp_updateextendedproperty
	@name sysname,
	@value sql_variant			= NULL,
	@level0type	varchar(128)	= NULL,
	@level0name	sysname			= NULL,
	@level1type	varchar(128)	= NULL,
	@level1name	sysname			= NULL,
	@level2type	varchar(128)	= NULL,
	@level2name	sysname			= NULL
as

	declare @id int
		,@smallid smallint
		,@type tinyint
		,@ret int
		,@exists int
		,@fullname nvarchar(400)
		,@objname		nvarchar(517)

	if datalength(@value) > 7500
	begin
		raiserror(15097,-1,-1)
		return 1
	end

	execute @ret = sp_validatepropertyinputs
						@name
						,@level0type
						,@level0name
						,@level1type
						,@level1name
						,@level2type
						,@level2name
						,@id			OUTPUT
						,@smallid		OUTPUT
						,@type			OUTPUT
						,@exists		OUTPUT
						,@fullname		OUTPUT
						,@objname		OUTPUT

	if @ret = 2
		raiserror(15600,-1,-1,'sp_updateextendedproperty')
	if @ret <> 0
		return (1)

	if ( @exists = 0 ) --Indicates property for object does not exist
	begin
		raiserror(15217,-1,-1,@name,@fullname)
		return(1)
	end

	BEGIN TRANSACTION

		if @objname is not null
			DBCC LockObjectSchema(@objname)

		update sysproperties set value = @value
		where		@type = type and
					@id =	id	and
					@smallid =	smallid	and
					@name = name

	COMMIT TRANSACTION

	return(0)
go


raiserror(15339,-1,-1,'sp_dropextendedproperty')
go
create procedure sp_dropextendedproperty
	@name sysname,
	@level0type	varchar(128)	= NULL,
	@level0name	sysname			= NULL,
	@level1type	varchar(128)	= NULL,
	@level1name	sysname			= NULL,
	@level2type	varchar(128)	= NULL,
	@level2name	sysname			= NULL
as

	declare @id int
		,@smallid smallint
		,@type int
		,@ret int
		,@exists int
		,@fullname nvarchar(400)
		,@objname		nvarchar(517)

	execute @ret = sp_validatepropertyinputs
						@name
						,@level0type
						,@level0name
						,@level1type
						,@level1name
						,@level2type
						,@level2name
						,@id			OUTPUT
						,@smallid		OUTPUT
						,@type			OUTPUT
						,@exists		OUTPUT
						,@fullname		OUTPUT
						,@objname		OUTPUT

	if @ret = 2
		raiserror(15600,-1,-1,'sp_dropextendedproperty')
	if @ret <> 0
		return (1)

	if (@exists = 0) --Indicates property for object does not exist
	begin
		raiserror(15217,-1,-1,@name,@fullname)
		return (1)
	end

	BEGIN TRANSACTION

		if @objname is not null
			DBCC LockObjectSchema(@objname)

  		delete from sysproperties
		where	@type = type and
				@id =	id	and
				@smallid =	smallid	and
				@name = name

	COMMIT TRANSACTION

	return(0)
go


/******************************************************************************
*************************  LOGIN-SECURITY (LOCAL)  ****************************
******************************************************************************/
checkpoint
go
if object_id('sp_addlogin','P') IS NOT NULL
	drop procedure sp_addlogin
if object_id('sp_password','P') IS NOT NULL
	drop procedure sp_password
if object_id('sp_droplogin','P') IS NOT NULL
	drop procedure sp_droplogin
if object_id('sp_MSaddlogin_implicit_ntlogin','P') IS NOT NULL
	drop procedure sp_MSaddlogin_implicit_ntlogin
if object_id('sp_grantlogin','P') IS NOT NULL
	drop procedure sp_grantlogin
if object_id('xp_grantlogin','P') IS NOT NULL
	drop procedure xp_grantlogin
if object_id('sp_validatelogins','P') IS NOT NULL
	drop procedure sp_validatelogins
if object_id('sp_denylogin','P') IS NOT NULL
	drop procedure sp_denylogin
if object_id('sp_revokelogin','P') IS NOT NULL
	drop procedure sp_revokelogin
if object_id('xp_revokelogin','P') IS NOT NULL
	drop procedure xp_revokelogin
if object_id('sp_defaultdb','P') IS NOT NULL
	drop procedure sp_defaultdb
if object_id('sp_defaultlanguage','P') IS NOT NULL
	drop procedure sp_defaultlanguage
if object_id('sp_addsrvrolemember','P') IS NOT NULL
	drop procedure sp_addsrvrolemember
if object_id('sp_dropsrvrolemember','P') IS NOT NULL
	drop procedure sp_dropsrvrolemember
go

------------------------------- sp_addlogin -----------------------------------

raiserror(15339,-1,-1,'sp_addlogin')
go
create procedure sp_addlogin
    @loginame		sysname
   ,@passwd         sysname = Null
   ,@defdb          sysname = 'master'      -- UNDONE: DEFAULT CONFIGURABLE???
   ,@deflanguage    sysname = Null
   ,@sid			varbinary(16) = Null
   ,@encryptopt		varchar(20) = Null
AS
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	Declare @ret    int    -- return value of sp call

    -- CHECK PERMISSIONS --
	IF (not is_srvrolemember('securityadmin') = 1)
	begin
	   dbcc auditevent (104, 1, 0, @loginame, NULL, NULL, @sid)
	   raiserror(15247,-1,-1)
	   return (1)
	end
	ELSE
	begin
	   dbcc auditevent (104, 1, 1, @loginame, NULL, NULL, @sid)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_addlogin')
		return (1)
	end

    -- VALIDATE LOGIN NAME AS:
    --  (1) Valid SQL Name (SQL LOGIN)
    --  (2) No backslash (NT users only)
    --  (3) Not a reserved login name
	execute @ret = sp_validname @loginame
	if (@ret <> 0)
        return (1)
    if (charindex('\', @loginame) > 0)
    begin
        raiserror(15006,-1,-1,@loginame)
        return (1)
    end

	--Note: different case sa is allowed.
	if (@loginame = 'sa' or lower(@loginame) in ('public'))
	begin
		raiserror(15405, -1 ,-1, @loginame)
		return (1)
	end

    -- LOGIN NAME MUST NOT ALREADY EXIST --
	if exists(select * from master.dbo.syslogins where loginname = @loginame)
	begin
		raiserror(15025,-1,-1,@loginame)
		return (1)
	end

	-- VALIDATE DEFAULT DATABASE --
	IF db_id(@defdb) IS NULL
	begin
		raiserror(15010,-1,-1,@defdb)
	    return (1)
	end

	-- VALIDATE DEFAULT LANGUAGE --
	IF (@deflanguage IS NOT Null)
	begin
		Execute @ret = sp_validlang @deflanguage
		IF (@ret <> 0)
			return (1)
	end
	ELSE
	begin
		select @deflanguage = name from master.dbo.syslanguages
		where langid = @@default_langid	--server default language

		if @deflanguage is null
			select @deflanguage = N'us_english'
	end

	-- VALIDATE SID IF GIVEN --
	if ((@sid IS NOT Null) and (datalength(@sid) <> 16))
	begin
		raiserror(15419,-1,-1)
	 	return (1)
	end
	else if @sid is null
		select @sid = newid()
	if (suser_sname(@sid) IS NOT Null)
	begin
		raiserror(15433,-1,-1)
	 	return (1)
	end

	-- VALIDATE AND USE ENCRYPTION OPTION --
	declare @xstatus smallint
	select @xstatus = 2	-- access
	if @encryptopt is null
		select @passwd = pwdencrypt(@passwd)
	else if @encryptopt = 'skip_encryption_old'
	begin
		select @xstatus = @xstatus | 0x800,	-- old-style encryption
			@passwd = convert(sysname, convert(varbinary(30), convert(varchar(30), @passwd)))
	end
	else if @encryptopt <> 'skip_encryption'
	begin
		raiserror(15600,-1,-1,'sp_addlogin')
		return 1
	end

    -- ATTEMPT THE INSERT OF THE NEW LOGIN --
	INSERT INTO master.dbo.sysxlogins VALUES
        (NULL, @sid, @xstatus, getdate(),
            getdate(), @loginame, convert(varbinary(256), @passwd),
            db_id(@defdb), @deflanguage)
	if @@error <> 0		-- this indicates we saw duplicate row
        return (1)

	-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
	exec('use master grant all to null')

    -- FINALIZATION: RETURN SUCCESS/FAILURE --
	raiserror(15298,-1,-1)
	return  (0)	-- sp_addlogin
go

------------------------------- sp_password -----------------------------------

raiserror(15339,-1,-1,'sp_password')
go
create procedure sp_password
    @old sysname = NULL,        -- the old (current) password
    @new sysname,               -- the new password
    @loginame sysname = NULL    -- user to change password on
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
    declare @self int
    select @self = CASE WHEN @loginame is null THEN 1 ELSE 2 END

    -- RESOLVE LOGIN NAME
    if @loginame is null
        select @loginame = suser_sname()

    -- CHECK PERMISSIONS (SecurityAdmin per Richard Waymire) --
	IF (not is_srvrolemember('securityadmin') = 1)
        AND not @self = 1
	begin
	   dbcc auditevent (107, @self, 0, @loginame, NULL, NULL, NULL)
	   raiserror(15210,-1,-1)
	   return (1)
	end
	ELSE
	begin
	   dbcc auditevent (107, @self, 1, @loginame, NULL, NULL, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_password')
		return (1)
	end

    -- RESOLVE LOGIN NAME (disallows nt names)
    if not exists (select * from master.dbo.syslogins where
                    loginname = @loginame and isntname = 0)
	begin
		raiserror(15007,-1,-1,@loginame)
		return (1)
	end

	-- IF non-SYSADMIN ATTEMPTING CHANGE TO SYSADMIN, REQUIRE PASSWORD (218078) --
	if (@self <> 1 AND is_srvrolemember('sysadmin') = 0 AND exists
			(SELECT * FROM master.dbo.syslogins WHERE loginname = @loginame and isntname = 0
				AND sysadmin = 1) )
		SELECT @self = 1

    -- CHECK OLD PASSWORD IF NEEDED --
    if (@self = 1 or @old is not null)
        if not exists (select * from master.dbo.sysxlogins
                        where srvid IS NULL and
						      name = @loginame and
			                  ( (@old is null and password is null) or
                              (pwdcompare(@old, password, (CASE WHEN xstatus&2048 = 2048 THEN 1 ELSE 0 END)) = 1) )   )
        begin
		    raiserror(15211,-1,-1)
		    return (1)
	    end

    -- CHANGE THE PASSWORD --
    update master.dbo.sysxlogins
	set password = convert(varbinary(256), pwdencrypt(@new)), xdate2 = getdate(), xstatus = xstatus & (~2048)
	where name = @loginame and srvid IS NULL

	-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
	exec('use master grant all to null')

    -- FINALIZATION: RETURN SUCCESS/FAILURE --
	if @@error <> 0
        return (1)
    raiserror(15478,-1,-1)
	return  (0)	-- sp_password
go

checkpoint
go
------------------------------- sp_droplogin ----------------------------------

raiserror(15339,-1,-1,'sp_droplogin')
go
create procedure sp_droplogin
	@loginame sysname
as

declare @exec_stmt nvarchar(890)

    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare	@sid	varbinary(85)

	/*Create temp tables before any DML to ensure dynamic*/
    -- CREATE TEMPORARY TABLES FOR LATER USE --
   	create table #db_list (dbname sysname collate database_default not null, user_name sysname collate database_default not null)
	create table #retval (job_count int not null)

    -- CHECK PERMISSIONS --
	IF (not is_srvrolemember('securityadmin') = 1)
	begin
	   dbcc auditevent (104, 2, 0, @loginame, NULL, NULL, NULL)
	   raiserror(15247,-1,-1)
	   return (1)
	end
	ELSE
	begin
	   dbcc auditevent (104, 2, 1, @loginame, NULL, NULL, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_droplogin')
		return (1)
	end

    -- CANNOT CHANGE SA ROLES --
    if @loginame = 'sa'
    begin
        raiserror(15405, -1 ,-1, @loginame)
        return (1)
    end

    -- VALIDATE LOGIN NAME (SQL LOGIN) --
	select @sid = sid from master.dbo.syslogins
        where loginname = @loginame and isntname = 0
	if (@sid is null)
	begin
		raiserror(15007,10,-1,@loginame)
		return(1)
	end

	-- CHECK IF @sid IS CURRENTLY LOGGED IN (ignore cached remote connections) --
	if exists(select * from master.dbo.sysprocesses where sid = @sid and status != 'dormant')
	begin
		raiserror(15434, -1, -1, @loginame)
		return(1)
	end

    -- CHECK IF ANY DATABASES ARE OWNED BY LOGIN --
	if exists(select * from master.dbo.sysdatabases where sid = @sid)
	begin
		raiserror(15174, -1, -1, @loginame)
		select 'Databases owned by login:' = name
                from master.dbo.sysdatabases where sid = @sid
		return(1)
	end

	-- COLLECT ALL INSTANCES OF USE OF THIS LOGIN IN SYSUSERS --
	declare @dbname		sysname
	declare ms_crs_dbname cursor local keyset for select name from master.dbo.sysdatabases
	open ms_crs_dbname
	fetch ms_crs_dbname into @dbname
	while @@fetch_status >= 0
	begin
		if (has_dbaccess(@dbname) = 1)
		begin
			select @exec_stmt = 'use ' + quotename( @dbname , '[') + '
				   insert into #db_list (dbname, user_name)
				select N'+ quotename( @dbname , '''')+', name from sysusers
				where sid = suser_sid(N' + quotename( @loginame , '''') + ') '
			exec (@exec_stmt)
		end
		else
			raiserror(15622,-1,-1, @dbname)

		fetch ms_crs_dbname into @dbname
	end
	deallocate ms_crs_dbname

    -- ERROR IF LOGIN USED AS USER IN ANY DATABASE --
	if (select count(*) from #db_list) <> 0
	begin
		raiserror(15175,-1,-1,@loginame)
		select
			'Database name:' = dbname,
			'User name:' = user_name,
			'Mapping type:' = 'user'
		from #db_list
		order by dbname
		return (1)
	end

    -- VERIFY NO JOBS IN MSDB OWNED BY THIS LOGIN --
	if db_id('msdb') is not null
        and object_id('msdb.dbo.sp_check_for_owned_jobs') is not null
	begin
        exec msdb.dbo.sp_check_for_owned_jobs @loginame, '#retval'
	    if exists (select job_count from #retval where job_count > 0)
	    begin
		    declare @job_count int
		    select @job_count = job_count from #retval
		    raiserror(14248, -1, -1, @job_count)
		    return (1)
	    end
	end

    -- DELETE THIS LOGIN (ALSO DELETES REMOTE LOGINS MAPPED TO IT) --
	delete from master.dbo.sysxlogins where sid = @sid

    -- FINALIZATION: SUCCESS/FAILURE MESSAGE
	if @@rowcount > 0
	begin
		-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
		exec('use master grant all to null')

		raiserror(15479,-1,-1)
		return (0)
	end
	else
	begin
		raiserror(15007,10,-1,@loginame)
		return (1)
	end     -- sp_droplogin
go

----------------------- sp_MSaddlogin_implicit_ntlogin ------------------------

raiserror(15339,-1,-1,'sp_MSaddlogin_implicit_ntlogin')
go
----------------------------------------
-- NOTE: FOR INTERNAL SECURITY USE ONLY!
--      DO NOT DOCUMENT OR USE!
----------------------------------------
create procedure sp_MSaddlogin_implicit_ntlogin
    @loginame		sysname
AS

declare @default_lang sysname

    -- NO-OP IF LOGIN ALREADY EXISTS --
	-- if suser_sid(@loginame) is null

	if not exists(select * from master.dbo.syslogins where loginname = @loginame)
	begin

        -- MUST BE NT NAME --
        if (charindex('\', @loginame) = 0)
            return (1)

	    declare	@newsid	varbinary(85),
                @status smallint

        -- OBTAIN NT SID FOR THIS LOGIN (SET STATUS BITS) --
        select @status = 4      -- ntlogin(4)
	    select @newsid = get_sid('\U'+@loginame, NULL)	    -- NT user
	    if (@newsid IS Null)
	    begin
            select @newsid = get_sid('\G'+@loginame, NULL)  -- NT group
	        IF (@newsid IS Null)
		        return (1)
	    end
        else
            select @status = @status | 8    -- NTUser
		
		-- FAIL IF SID ALREADY IN SYSLOGINS 
		if exists(select * from master.dbo.syslogins where sid = @newsid)
			return (1)

		select @default_lang = name from master.dbo.syslanguages
		where langid = @@default_langid 	--server default language


        -- ADD IMPLICIT LOGIN ENTRY --
	    INSERT into master.dbo.sysxlogins Values
            (NULL, @newsid, @status, getdate(), getdate(),
                @loginame, NULL, 1, isnull(@default_lang, N'us_english'))
		if @@error <> 0		-- this indicates we saw duplicate row
			return @@error

		-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
		exec('use master grant all to null')

    end

    -- RETURN FAILURE/SUCCESS
    return (0) -- sp_MSaddlogin_implicit_ntlogin
go

------------------------------- sp_grantlogin ---------------------------------

raiserror(15339,-1,-1,'sp_grantlogin')
go
create procedure sp_grantlogin
    @loginame		sysname
AS
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret   int    -- return value of sp call

    -- CHECK PERMISSIONS --
	IF (not is_srvrolemember('securityadmin') = 1)
	begin
	   dbcc auditevent (105, 1, 0, @loginame, NULL, NULL, NULL)
	   raiserror(15247,-1,-1)
	   return (1)
	end
	ELSE
	begin
	   dbcc auditevent (105, 1, 1, @loginame, NULL, NULL, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_grantlogin')
		return (1)
	end

    -- DISALLOW SQL LOGIN (IE. MUST BE 'DOMAIN\USER') --
	if (charindex('\', @loginame) = 0)
	begin
		raiserror(15407, -1, -1, @loginame)
		return (1)
	end

    -- ADD ROW FOR NT LOGIN IF NEEDED --
	if not exists(select * from master.dbo.syslogins where loginname = @loginame)
    begin
        execute @ret = sp_MSaddlogin_implicit_ntlogin @loginame
        if (@ret <> 0)
	    begin
		    raiserror(15401,-1,-1 ,@loginame)
		    return (1)
	    end
    end

    -- UPDATE LOGIN BITS --
    update master.dbo.sysxlogins set xstatus = (xstatus & ~1) | 2, xdate2 = getdate()
        where name = @loginame and srvid IS NULL

	-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
	exec('use master grant all to null')

    -- FINALIZATION: RETURN SUCCESS/FAILURE
	if @@error <> 0
	begin
		raiserror(15480,-1,-1,@loginame)
        return (1)
	end
	else
	begin
		raiserror(15481,-1,-1,@loginame)
        return (0)
	end -- sp_grantlogin
go

------------------------------- sp_validatelogins ---------------------------------

raiserror(15339,-1,-1,'sp_validatelogins')
go
create proc sp_validatelogins
AS
	-- Must be securityadmin (or sysadmin) to execute
	if is_srvrolemember('securityadmin') = 0 and is_srvrolemember('sysadmin') = 0
	begin
		raiserror(15247,-1,-1)
		return 1
	end

	-- Use get_sid() to determine if nt name is still valid (builtin is only available from system procs!)
	select 'SID' = sid, 'NT Login' = loginname from master.dbo.syslogins
		where isntname = 1 and get_sid(loginname, NULL) is null
	return 0 -- sp_validatelogins
go

-- FOR BACKWARD COMPATIBILTY ONLY --
raiserror(15339,-1,-1,'xp_grantlogin')
go
create procedure xp_grantlogin
    @loginame       sysname,
    @logintype      varchar(5) = Null       -- ignored unless 'admin'
AS
	set nocount on

    -- IF NAME NOT 'DOMAIN\USER', ADD DEFAULT DOMAIN --
    if (charindex('\', @loginame) = 0)
    begin
        declare @defdom varchar(25)
        exec master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE',
                'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
                'DefaultDomain', @defdom out
        select @loginame = @defdom + '\' + @loginame
    end

	Declare @ret   int     -- return value of sp call
    execute @ret = sp_grantlogin @loginame
    if (@ret = 0 and @logintype = 'admin')
        execute @ret = sp_addsrvrolemember @loginame, 'sysadmin'
    return (@ret)
go

checkpoint
go
------------------------------- sp_denylogin ----------------------------------

raiserror(15339,-1,-1,'sp_denylogin')
go
create procedure sp_denylogin
    @loginame		sysname
AS
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret   int    -- return value of sp call

    -- CHECK PERMISSIONS --
	IF (not is_srvrolemember('securityadmin') = 1)
	begin
	   dbcc auditevent (105, 3, 0, @loginame, NULL, NULL, NULL)
	   raiserror(15247,-1, -1)
	   return (1)
	end
	ELSE
	begin
	   dbcc auditevent (105, 3, 1, @loginame, NULL, NULL, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_denylogin')
		return (1)
	end

    -- DISALLOW SQL LOGIN (IE. MUST BE 'DOMAIN\USER') --
	if (charindex('\', @loginame) = 0)
	begin
		raiserror(15407, -1, -1, @loginame)
		return (1)
	end

    -- ADD ROW FOR NT LOGIN IF NEEDED --
	if not exists(select * from master.dbo.syslogins where loginname = @loginame)
    begin
        execute @ret = sp_MSaddlogin_implicit_ntlogin @loginame
        if (@ret <> 0)
	    begin
		    raiserror(15401,-1,-1 ,@loginame)
		    return (1)
	    end
    end

    -- UPDATE LOGIN BITS --
    update master.dbo.sysxlogins set xstatus = (xstatus & ~2) | 1, xdate2 = getdate()
        where name = @loginame and srvid IS NULL

	-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
	exec('use master grant all to null')

    -- FINALIZATION: RETURN SUCCESS/FAILURE
	if @@error <> 0
	begin
		raiserror(15482,-1,-1,@loginame)
        return (1)
	end
	else
	begin
		raiserror(15483,-1,-1,@loginame)
        return (0)
	end -- sp_denylogin
go

------------------------------- sp_revokelogin --------------------------------

raiserror(15339,-1,-1,'sp_revokelogin')
go
create procedure sp_revokelogin
    @loginame		sysname
AS
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare	@sid	varbinary(85)

    -- CHECK PERMISSIONS --
	IF (not is_srvrolemember('securityadmin') = 1)
	begin
	   dbcc auditevent (105, 2, 0, @loginame, NULL, NULL, NULL)
	   raiserror(15247,-1,-1)
	   return (1)
	end
	ELSE
	begin
	   dbcc auditevent (105, 2, 1, @loginame, NULL, NULL, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_revokelogin')
		return (1)
	end

    -- DISALLOW SQL LOGIN (IE. MUST BE 'DOMAIN\USER') --
	if (charindex('\', @loginame) = 0)
	begin
		raiserror(15407, -1, -1, @loginame)
		return (1)
	end

    -- REMOVE ROW IF EXISTS FOR LOGIN PROVIDED IT IS AN NT NAME --
    -- select @sid = suser_sid(@loginame)
	-- if @sid is not null
	if exists(select * from master.dbo.syslogins where loginname = @loginame and isntname = 1)
	begin
		select @sid = sid from master.dbo.syslogins where loginname = @loginame and isntname = 1

        -- For nt logins, skip sid foreign-key checks. --
        -- also deletes remote logins mapped to this user --
        delete from master.dbo.sysxlogins where sid = @sid

		-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
		exec('use master grant all to null')
	end
	else
	begin
		-- CHECK IF THIS IS A VALID NT NAME BY GETTING ITS SID FROM NT
		declare	@newsid	varbinary(85)
		select @newsid = get_sid('\U'+@loginame, NULL)	    -- NT user
		IF (@newsid IS Null)
		begin
			select @newsid = get_sid('\G'+@loginame, NULL)  -- NT group
			IF (@newsid IS Null)
			begin
				raiserror(15401,-1,-1 ,@loginame)
				return (1)
			end
		end
	end


    -- FINALIZATION: RETURN SUCCESS/FAILURE
	if @@error <> 0
	begin
		raiserror(15484,-1,-1,@loginame)
        return (1)
	end
	else
	begin
		raiserror(15485,-1,-1,@loginame)
        return (0)
	end -- sp_revokelogin
go


-- FOR BACKWARD COMPATIBILTY ONLY --
raiserror(15339,-1,-1,'xp_revokelogin')
go
create procedure xp_revokelogin
    @loginame       sysname
AS
	set nocount on

    -- IF NAME NOT 'DOMAIN\USER', ADD DEFAULT DOMAIN --
    if (charindex('\', @loginame) = 0)
    begin
        declare @defdom varchar(25)
        exec master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE',
                'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
                'DefaultDomain', @defdom out
        select @loginame = @defdom + '\' + @loginame
    end

	Declare @ret   int     -- return value of sp call
    execute @ret = sp_revokelogin @loginame
    return (@ret)
go

------------------------------- sp_defaultdb ----------------------------------

raiserror(15339,-1,-1,'sp_defaultdb')
go
create procedure sp_defaultdb
    @loginame   sysname,	-- login name
    @defdb      sysname     -- default db
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret   int    -- return value of sp call

    -- CHECK PERMISSIONS --
	IF (not is_srvrolemember('securityadmin') = 1)
        AND not @loginame = suser_sname()
	begin
	    dbcc auditevent (106, 1, 0, @loginame, NULL, NULL, NULL)
		raiserror(15132,-1,-1)
		return (1)
	end
	ELSE
	begin
	   dbcc auditevent (106, 1, 1, @loginame, NULL, NULL, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_defaultdb')
		return (1)
	end


    -- VALIDATE DATABASE NAME --
    if db_id(@defdb) IS NULL
	begin
		raiserror(15010,-1,-1,@defdb)
		return (1)
	end

    -- ADD ROW FOR NT LOGIN IF NEEDED --
	if not exists(select * from master.dbo.syslogins where loginname = @loginame)
    begin
        execute @ret = sp_MSaddlogin_implicit_ntlogin @loginame
        if (@ret <> 0)
	    begin
		    raiserror(15007,-1,-1,@loginame)
		    return (1)
	    end
    end

    -- CHANGE DEFAULT DATABASE --
    update master.dbo.sysxlogins set dbid = db_id(@defdb), xdate2 = getdate()
	    where name = @loginame and srvid IS NULL

	-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
	exec('use master grant all to null')

    -- FINALIZATION: RETURN SUCCESS/FAILURE
	IF (@@error <> 0)
	    return (1)
    raiserror(15486,-1,-1)
	return (0) -- sp_defaultdb
go

checkpoint
go
---------------------------- sp_defaultlanguage -------------------------------

raiserror(15339,-1,-1,'sp_defaultlanguage')
go
create procedure sp_defaultlanguage
    @loginame sysname,			-- login name
    @language sysname = NULL	-- default language
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret   int     -- return value of sp call

    -- CHECK PERMISSIONS --
	IF (not is_srvrolemember('securityadmin') = 1)
        AND not @loginame = suser_sname()
	begin
	    dbcc auditevent (106, 2, 0, @loginame, NULL, NULL, NULL)
		raiserror(15247,-1,-1)
		return (1)
	end
	ELSE
	begin
	   dbcc auditevent (106, 2, 1, @loginame, NULL, NULL, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_defaultlanguage')
		return (1)
	end

	-- VALIDATE LANGUAGE --
	IF (@language is not Null)
	begin
		Execute @ret = sp_validlang @language
		IF (@ret <> 0)
			return (1)
	end
	else
	begin

		select @language = name from master.dbo.syslanguages
		where langid = @@default_langid  --default language

		if @language is null
			select @language = N'us_english'
	end

    -- ADD ROW FOR NT LOGIN IF NEEDED --
	if not exists(select * from master.dbo.syslogins where loginname = @loginame)
    begin
        execute @ret = sp_MSaddlogin_implicit_ntlogin @loginame
        if (@ret <> 0)
	    begin
		    raiserror(15007,-1,-1,@loginame)
		    return (1)
	    end
    end

    -- CHANGE DEFAULT LANGUAGE --
    update master.dbo.sysxlogins set language = @language, xdate2 = getdate()
	    where name = @loginame and srvid IS NULL

	-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
	exec('use master grant all to null')

    -- FINALIZATION: RETURN SUCCESS/FAILURE
	if @@error <> 0
    	return (1)
    raiserror(15487,-1,-1,@loginame,@language)
	return (0) -- sp_defaultlanguage
go

--------------------------- sp_addsrvrolemember -------------------------------

raiserror(15339,-1,-1,'sp_addsrvrolemember')
go
create procedure sp_addsrvrolemember
    @loginame sysname,			-- login name
    @rolename sysname = NULL	-- server role name
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret        int,    -- return value of sp call
            @rolebit    smallint,
            @ismem      int

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_addsrvrolemember')
		return (1)
	end

    -- VALIDATE SERVER ROLE NAME, CHECKING PERMISSIONS --
    select @ismem = is_srvrolemember(@rolename)
    if @ismem is null
    begin
		dbcc auditevent (108, 1, 0, @loginame, NULL, @rolename, NULL)
        raiserror(15402, -1, -1, @rolename)
        return (1)
    end
    if @ismem = 0
	begin
		dbcc auditevent (108, 1, 0, @loginame, NULL, @rolename, NULL)
		raiserror(15247,-1,-1)
		return (1)
	end

	-- AUDIT A SUCCESSFUL SECURITY CHECK --
	dbcc auditevent (108, 1, 1, @loginame, NULL, @rolename, NULL)

    -- CANNOT CHANGE SA ROLES --
    if @loginame = 'sa'
    begin
        raiserror(15405, -1 ,-1, @loginame)
        return (1)
    end

    -- OBTAIN THE BIT FOR THIS ROLE --
    select @rolebit = CASE @rolename
            WHEN 'sysadmin'         THEN 16
            WHEN 'securityadmin'    THEN 32
            WHEN 'serveradmin'      THEN 64
            WHEN 'setupadmin'       THEN 128
            WHEN 'processadmin'     THEN 256
            WHEN 'diskadmin'        THEN 512
            WHEN 'dbcreator'        THEN 1024
			WHEN 'bulkadmin'		THEN 4096
            ELSE NULL END

    -- ADD ROW FOR NT LOGIN IF NEEDED --
	if not exists(select * from master.dbo.syslogins where loginname = @loginame)
    begin
        execute @ret = sp_MSaddlogin_implicit_ntlogin @loginame
        if (@ret <> 0)
	    begin
		    raiserror(15007,-1,-1,@loginame)
		    return (1)
	    end
    end

    -- UPDATE ROLE MEMBERSHIP --
    update master.dbo.sysxlogins set xstatus = xstatus | @rolebit, xdate2 = getdate()
	    where name = @loginame and srvid IS NULL

	-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
	exec('use master grant all to null')

	raiserror(15488,-1,-1,@loginame,@rolename)

    -- FINALIZATION: RETURN SUCCESS/FAILURE
	return (@@error) -- sp_addsrvrolemember
go

checkpoint
go
--------------------------- sp_dropsrvrolemember ------------------------------

raiserror(15339,-1,-1,'sp_dropsrvrolemember')
go
create procedure sp_dropsrvrolemember
    @loginame sysname,			-- login name
    @rolename sysname = NULL	-- server role name
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret        int,    -- return value of sp call
            @rolebit    smallint,
            @ismem      int

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_dropsrvrolemember')
		return (1)
	end

    -- VALIDATE SERVER ROLE NAME, CHECKING PERMISSIONS --
    select @ismem = is_srvrolemember(@rolename)
    if @ismem is null
    begin
		dbcc auditevent (108, 2, 0, @loginame, NULL, @rolename, NULL)
        raiserror(15402, -1, -1, @rolename)
        return (1)
    end
    if @ismem = 0
	begin
		dbcc auditevent (108, 2, 0, @loginame, NULL, @rolename, NULL)
		raiserror(15247,-1,-1)
		return (1)
	end

	-- AUDIT THE SUCCESSFUL SECURITY CHECK --
	dbcc auditevent (108, 2, 1, @loginame, NULL, @rolename, NULL)

    -- CANNOT CHANGE SA ROLES --
    if @loginame = 'sa'
    begin
        raiserror(15405, -1 ,-1, @loginame)
        return (1)
    end

    -- OBTAIN THE BIT FOR THIS ROLE --
    select @rolebit = CASE @rolename
            WHEN 'sysadmin'         THEN 16
            WHEN 'securityadmin'    THEN 32
            WHEN 'serveradmin'      THEN 64
            WHEN 'setupadmin'       THEN 128
            WHEN 'processadmin'     THEN 256
            WHEN 'diskadmin'        THEN 512
            WHEN 'dbcreator'        THEN 1024
			WHEN 'bulkadmin'		THEN 4096
            ELSE NULL END

    -- ERROR IF USER DOESNT EXIST --
	if not exists(select * from master.dbo.syslogins where loginname = @loginame)
    begin
	    raiserror(15007,-1,-1,@loginame)
	    return (1)
    end

    -- UPDATE ROLE MEMBERSHIP --
    update master.dbo.sysxlogins set xstatus = xstatus & ~@rolebit, xdate2 = getdate()
	    where name = @loginame and srvid IS NULL

	-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
	exec('use master grant all to null')

	raiserror(15489,-1,-1,@loginame,@rolename)

    -- FINALIZATION: RETURN SUCCESS/FAILURE
	return (@@error) -- sp_dropsrvrolemember
go

-- GRANT PUBLIC ACCESS (SP'S DO INTERNAL PERMISSIONS CHECKS) --
grant execute on sp_addlogin to public
grant execute on sp_password to public
grant execute on sp_droplogin to public
grant execute on sp_grantlogin to public
grant execute on sp_validatelogins to public
grant execute on xp_grantlogin to public
grant execute on sp_denylogin to public
grant execute on sp_revokelogin to public
grant execute on xp_revokelogin to public
grant execute on sp_defaultdb to public
grant execute on sp_defaultlanguage to public
grant execute on sp_addsrvrolemember to public
grant execute on sp_dropsrvrolemember to public
grant execute on sp_attach_db to public
grant execute on sp_attach_single_file_db to public
go

/**************************  END LOGIN-SECURITY ******************************/


/******************************************************************************
************************  DATABASE-ACCESS-SECURITY  ***************************
******************************************************************************/
checkpoint
go
if object_id('sp_MSadduser_implicit_ntlogin','P') IS NOT NULL
	drop procedure sp_MSadduser_implicit_ntlogin
if object_id('sp_MScheck_uid_owns_anything','P') IS NOT NULL
	drop procedure sp_MScheck_uid_owns_anything
if object_id('sp_grantdbaccess','P') IS NOT NULL
	drop procedure sp_grantdbaccess
if object_id('sp_revokedbaccess','P') IS NOT NULL
	drop procedure sp_revokedbaccess
if object_id('sp_adduser','P') IS NOT NULL
	drop procedure sp_adduser
if object_id('sp_dropuser','P') IS NOT NULL
	drop procedure sp_dropuser
if object_id('sp_addalias','P') IS NOT NULL
	drop procedure sp_addalias
if object_id('sp_dropalias','P') IS NOT NULL
	drop procedure sp_dropalias
if object_id('sp_addrole','P') IS NOT NULL
	drop procedure sp_addrole
if object_id('sp_droprole','P') IS NOT NULL
	drop procedure sp_droprole
if object_id('sp_addgroup','P') IS NOT NULL
	drop procedure sp_addgroup
if object_id('sp_dropgroup','P') IS NOT NULL
	drop procedure sp_dropgroup
if object_id('sp_addapprole','P') IS NOT NULL
	drop procedure sp_addapprole
if object_id('sp_approlepassword','P') IS NOT NULL
	drop procedure sp_approlepassword
if object_id('sp_setapprole','P') IS NOT NULL
	drop procedure sp_setapprole
if object_id('sp_dropapprole','P') IS NOT NULL
	drop procedure sp_dropapprole
if object_id('sp_addrolemember','P') IS NOT NULL
	drop procedure sp_addrolemember
if object_id('sp_droprolemember','P') IS NOT NULL
	drop procedure sp_droprolemember
if object_id('sp_changegroup','P') IS NOT NULL
	drop procedure sp_changegroup
if object_id('sp_change_users_login','P') IS NOT NULL
    drop procedure sp_change_users_login
if object_id('sp_changedbowner','P') IS NOT NULL
	drop procedure sp_changedbowner
if object_id('sp_check_removable_sysusers','P') IS NOT NULL
	drop procedure sp_check_removable_sysusers
if object_id('sp_changeobjectowner', 'P') IS NOT NULL
	drop procedure sp_changeobjectowner
go

----------------------- sp_MSadduser_implicit_ntlogin -------------------------

raiserror(15339,-1,-1,'sp_MSadduser_implicit_ntlogin')
go
----------------------------------------
-- NOTE: FOR INTERNAL SECURITY USE ONLY!
--      DO NOT DOCUMENT OR USE!
----------------------------------------
create procedure sp_MSadduser_implicit_ntlogin
    @ntname         sysname
AS
    -- NO-OP IF LOGIN ALREADY EXISTS --
	if user_id(@ntname) is null
	begin

        -- MUST BE NT NAME --
        if (charindex('\', @ntname) = 0)
            return (1)

	    declare	@newsid	varbinary(85),
                @status smallint,
                @uid    smallint

        -- OBTAIN NT SID FOR THIS USER (SET STATUS BITS) --
        select @status = 4      -- ntlogin(4)
	    select @newsid = get_sid('\U'+@ntname, NULL)	    -- NT user
	    if (@newsid is Null)
	    begin
	        select @newsid = get_sid('\G'+@ntname, NULL)    -- NT group
	        IF (@newsid IS Null)
		        return (1)
	    end
        else
            select @status = @status | 8    -- NTUser

        -- FAIL IF SID ALREADY IN SYSUSERS --
        if exists (select sid from sysusers where sid = @newsid)
            return (1)

        -- OBTAIN NEW UID (RESERVE 1-4) --
        if user_name(5) IS NULL
            select @uid = 5
        else
		    select @uid = min(uid)+1 from sysusers
                where uid >= 5 and uid < (16384 - 1)    -- stay in users range
                    and user_name(uid+1) is null        -- uid not in use
        if @uid is null
	    begin
		    raiserror(15065,-1,-1)
		    return (1)
	    end

        -- ADD IMPLICIT SYSUSERS ENTRY --
        insert into sysusers values
            (@uid, @status, @ntname, @newsid, 0x00, getdate(), getdate(), 0, NULL)

        -- INVALIDATE UID CACHE FOR THIS DB --
        grant all to null
    end

    -- RETURN FAILURE/SUCCESS --
    return @@error -- sp_MSadduser_implicit_ntlogin
go

------------------------ sp_MScheck_uid_owns_anything -------------------------

raiserror(15339,-1,-1,'sp_MScheck_uid_owns_anything')
go
----------------------------------------
-- NOTE: FOR INTERNAL SECURITY USE ONLY!
--      DO NOT DOCUMENT OR USE!
----------------------------------------
create procedure sp_MScheck_uid_owns_anything
    @uid            smallint        -- uid to for which to check ownership
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @isowner    int
    select @isowner = 0

    -- CHECK IF USER OWNS ANY OBJECTS --
    select @isowner = 0
    if exists (select uid from sysobjects where uid = @uid)
	begin
		raiserror(15183,-1,-1)
		select name, type from sysobjects where uid = @uid
        select @isowner = 1
	end

    -- CHECK IF USER OWNS ANY TYPES --
    if exists (select uid from systypes where uid = @uid)
    begin
		raiserror(15184,-1,-1)
		select user_type = name, physical_type = type_name(xtype)
					from systypes where uid = @uid
        select @isowner = 1
	end

    -- CHECK IF USER GRANTED ANY PERMISSIONS --
    if exists (select grantor from syspermissions where grantor = @uid)
    begin
		raiserror(15284,-1,-1)
		select 'Grantee'=user_name(grantee) ,'Object'=object_name(id)
					from syspermissions where grantor = @uid
        select @isowner = 1
	end

    -- CHECK IF USER OWNS ANY ROLES --
    if exists (select altuid from sysusers where altuid = @uid
                and (issqlrole = 1 or isapprole = 1))
    begin
		raiserror(15421,-1,-1)
		select 'Role Name' = name,
               'Type' = CASE WHEN issqlrole=1 THEN 'SQL Role'
                             ELSE 'App Role' END
            from sysusers where altuid = @uid and (issqlrole = 1 or isapprole = 1)
        select @isowner = 1
    end

    return @isowner	-- sp_MScheck_uid_owns_anything
go

------------------------------ sp_grantdbaccess -------------------------------

raiserror(15339,-1,-1,'sp_grantdbaccess')
go
create procedure sp_grantdbaccess
	@loginame       sysname,
	@name_in_db     sysname = NULL OUT
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret        int,    -- return value of sp call
            @uid        smallint,
            @sid        varbinary(85),
            @status     smallint

    if @name_in_db is null
        select @name_in_db = @loginame

    -- CHECK PERMISSIONS --
    if (not is_member('db_accessadmin') = 1) and
       (not is_member('db_owner') = 1)
	begin
        dbcc auditevent (109, 3, 0, @loginame, @name_in_db, NULL, NULL)
		raiserror(15247,-1,-1)
		return (1)
	end
	else
	begin
        dbcc auditevent (109, 3, 1, @loginame, @name_in_db, NULL, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_grantdbaccess')
		return (1)
	end

    -- VALIDATE NAME-IN-DB --
    if @name_in_db <> @loginame
    begin
		exec @ret = sp_validname @name_in_db
		if @ret <> 0
			return(1)
        if (charindex('\', @name_in_db) > 0)
        begin
            raiserror(15006,-1,-1,@name_in_db)
            return (1)
        end
    end

    -- CHECK FOR SPECIAL USER GUEST --
    if @name_in_db = 'guest'
    begin
        -- ERROR IF NOT USER, OR ALREADY ADDED --
        if @loginame <> 'guest'
        begin
		    raiserror(15062,-1,-1)
		    return(1)
        end
        if exists (select * from sysusers where hasdbaccess = 1 and name = 'guest')
        begin
            raiserror(15023,-1,-1,'guest')
            return (1)
        end

        -- ENABLE USER GUEST --
        update sysusers set status = (status & ~1) | 2, updatedate = getdate()
                    where name = 'guest'
        return (0)
    end

    -- PREVENT USE OF CERTAIN LOGINS --
	if @loginame = 'sa'
	begin
		raiserror(15405, -1, -1, @loginame)
		return (1)
	end

    -- VALIDATE LOGIN NAME (OBTAIN SID) --
    select @status = case when (charindex('\', @loginame) <> 0) then 4 else 0 end
    if @status = 0
        select @sid = sid from master.dbo.syslogins         -- sql user
            where isntname = 0 and loginname = @loginame
    if @sid is null
    begin
        -- NT GROUPS REQUIRE DOMAIN NAME --
        if @status = 4
            select @sid = get_sid('\G'+@loginame, NULL)     -- nt group
        if @sid is null
        begin
            select @sid = get_sid('\U'+@loginame, NULL)     -- nt user
            if @sid is not null
                select @status = 12
        end
    end
    if @sid is null
    begin
        if @status = 0
            raiserror(15007,-1,-1,@loginame)
        else
            raiserror(15401,-1,-1,@loginame)
        return (1)
    end

    -- CHECK IF LOGIN ALREADY IN DATABASE --
    if exists (select sid from sysusers where sid = @sid)
    begin
        -- ERROR IF LOGIN IS ALREADY ALIASED --
        if exists (select sid from sysusers where sid = @sid and isaliased = 1)
        begin
		    raiserror(15022,-1,-1)
		    return (1)
        end

        -- ERROR IF ALREADY EXISTS UNDER DIFFERENT NAME --
        if (not user_sid(user_id(@name_in_db)) = @sid)
        begin
		    raiserror(15063,-1,-1)
		    return (1)
        end

        -- ERROR IF LOGIN ALREADY HAS ACCESS --
        if exists (select sid from sysusers where sid = @sid and hasdbaccess = 1)
        begin
            if @status = 4
    		    raiserror(15024,-1,-1,@name_in_db)
            else
		        raiserror(15023,-1,-1,@name_in_db)
		    return (1)
        end

        -- GIVE DATABASE ACCESS TO THIS LOGIN --
        update sysusers set status = (status & ~1) | 2, updatedate = getdate()
                    where sid = @sid
        return @@error
	end

    if user_id(@name_in_db) is not null OR
		@name_in_db IN ('system_function_schema','INFORMATION_SCHEMA')
    begin
        -- SYSUSERS NAME ALREADY EXISTS --
        if @status = 4
    		raiserror(15024,-1,-1,@name_in_db)
        else
		    raiserror(15023,-1,-1,@name_in_db)
        return (1)
    end

    -- OBTAIN NEW UID (RESERVE 1-4) --
    if user_name(5) IS NULL
        select @uid = 5
    else
		select @uid = min(uid)+1 from sysusers
            where uid >= 5 and uid < (16384 - 1)    -- stay in users 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 SYSUSERS ROW --
    insert into sysusers select
        @uid, @status | 2, @name_in_db, @sid, 0x00, getdate(), getdate(), 0, NULL

    -- INVALIDATE CACHED PERMISSIONS --
    grant all to null

    -- PRINT SUCCESS --
    raiserror(15341,-1,-1, @loginame)

    -- RETURN SUCCESS STATUS --
    return @@error -- sp_grantdbaccess
go

-- FOR BACKWARD COMPATIBILTY ONLY --
raiserror(15339,-1,-1,'sp_adduser')
go
create procedure sp_adduser
	@loginame       sysname,	    -- user's login name in syslogins
	@name_in_db     sysname = NULL, -- user's name to add to current db
	@grpname		sysname = NULL  -- role to which user should be added.
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret        int

    -- LIMIT TO SQL/NT USERS IN SYSLOGINS (BCKWRD COMPAT ONLY!)
	if not exists (select * from master.dbo.syslogins where loginname = @loginame
			and (isntuser = 1 or isntname = 0))
        and @loginame <> 'guest'
    begin
        raiserror(15007,-1,-1,@loginame)
        return (1)
    end

	-- VALIDATE THE ROLENAME --
    if @grpname is not null and
	   not exists (select * from sysusers where name = @grpname and issqlrole = 1)
    begin
	    raiserror(15014,-1,-1,@grpname)
	    return (1)
    end

    if @name_in_db is null
        select @name_in_db = @loginame

	-- In Hydra only the user dbo can do this --
    if (not is_member('dbo') = 1)
	begin
	    -- AUDIT FAILED SECURITY CHECK --
        dbcc auditevent (109, 1, 0, @loginame, @name_in_db, @grpname , NULL)
		raiserror(15247,-1,-1)
		return (1)
	end
    else
    begin
        -- AUDIT SUCCESSFUL SECURITY CHECK --
        dbcc auditevent (109, 1, 1, @loginame, @name_in_db, @grpname , NULL)
    end

    -- ADD THE USER TO THE DATABASE --
    execute @ret = sp_grantdbaccess @loginame, @name_in_db OUT
    if (@ret <> 0)
        return (1)

    -- ADD USER TO ROLE IF GIVEN. NOP FOR 'public' --
    if (@grpname is not null) and (@grpname <> 'public')
    begin
        execute @ret = sp_addrolemember @grpname, @name_in_db
        if @ret <> 0
		begin
			-- ROLL BACK THE ABOVE sp_grantdbaccess --
			if @name_in_db = 'guest'
				update sysusers set status = status & ~2, updatedate = getdate()
                            where name = 'guest'
			else
				delete from sysusers where name = @name_in_db
            return (1)
		end
    end

    -- RETURN SUCCESS --
    return (0) -- sp_adduser
go

checkpoint
go

----------------------------- sp_revokedbaccess -------------------------------

raiserror(15339,-1,-1,'sp_revokedbaccess')
go
create procedure sp_revokedbaccess
	@name_in_db     sysname
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @uid        smallint,
            @ret        int

    -- CHECK PERMISSIONS --
    if (not is_member('db_accessadmin') = 1) and
       (not is_member('db_owner') = 1)
	begin
		dbcc auditevent (109, 4, 0, NULL, @name_in_db, NULL, NULL)
		raiserror(15247,-1,-1)
		return (1)
	end
	else
	begin
		dbcc auditevent (109, 4, 1, NULL, @name_in_db, NULL, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_revokedbaccess')
		return (1)
	end

    -- CHECK IF SYSUSER EXISTS --
    select @uid = uid from sysusers where name = @name_in_db
            and (issqluser = 1 or isntname = 1)         -- is droppable entity
            and (name <> 'guest' or hasdbaccess = 1)    -- special case guest
    if @uid is null
    begin
		raiserror(15008,-1,-1,@name_in_db)
        return (1)
    end

    -- CANNOT DROP DBO/INFORMATION_SCHEMA/public --
	if @uid in (1,0,3,4) --dbo, public, INFORMATION_SCHEMA, system_function_schema
	begin
		raiserror(15181,-1,-1)
		return (1)
	end

    -- CANNOT DROP GUEST IN MASTER/TEMPDB --
	if lower(@name_in_db) = 'guest' and db_id() in (1, 2)
	begin
		raiserror(15182,-1,-1)
		return(1)
	end

    -- CHECK IF USER OWNS ANYTHING --
    execute @ret = sp_MScheck_uid_owns_anything @uid
    if @ret <> 0
        return (1)

    -- REMOVE SYSPERMISSIONS ROWS AND DEPENDENT ALIASES --
    delete from syspermissions where grantee = @uid
    if exists (select altuid from sysusers where altuid = @uid and isaliased = 1)
    begin
        delete from sysusers where altuid = @uid and isaliased = 1
       	raiserror(15490,-1,-1)
    end

    -- DROP USER: SPECIAL HANDLING FOR GUEST (REMOVE HASDBACCESS) --
    if lower(@name_in_db) = 'guest'
        update sysusers set status = status & ~2, updatedate = getdate()
			where uid = user_id('guest')
    else
	begin
	    delete from sysusers where uid = @uid

		delete from sysproperties where type =  2 and id = 0 and
		smallid = @uid

	end
    -- RETURN SUCCESS/FAILURE --
    if @@error <> 0
        return (1)

    -- INVALIDATE CACHED PERMISSIONS --
    grant all to null

    raiserror(15491,-1,-1)
    return (0) -- sp_revokedbaccess
go

-- FOR BACKWARD COMPATIBILTY ONLY --
raiserror(15339,-1,-1,'sp_dropuser')
go
create procedure sp_dropuser
	@name_in_db     sysname     -- user name to drop
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
    declare @ret    int
    declare @targetName sysname
    -- LIMIT TO SQL/NT USERS (BCKWRD COMPAT ONLY!)
    if not exists (select * from sysusers where name = @name_in_db
                    and (isntuser = 1 or isntname = 0))
    begin
        raiserror(15008,-1,-1,@name_in_db)
        return (1)
    end
    -- store target name
    select  @targetName = (select sl.name from master..syslogins sl,sysusers su where su.name = @name_in_db and su.sid = sl.sid)

    -- DROP THE USER FROM THE DATABASE --
    execute @ret = sp_revokedbaccess @name_in_db
    if @ret <> 0
    begin
        -- AUDIT FAILED SECURITY CHECK
    dbcc auditevent (109, 2, 0, @targetName, @name_in_db, NULL, NULL)
        return (1)
    end

    -- AUDIT SUCCESSFUL SECURITY CHECK --
    dbcc auditevent (109, 2, 1, @targetName, @name_in_db, NULL, NULL)

    -- FINALIZATION: PRINT/RETURN SUCCESS --
    return (0) -- sp_dropuser
go

-------------------------------- sp_addalias ----------------------------------

raiserror(15339,-1,-1,'sp_addalias')
go
create procedure sp_addalias
    @loginame       sysname,    -- name of the pretender
    @name_in_db     sysname     -- user to whom to alias the login
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @sid        varbinary(85),
            @targuid    smallint,
            @newuid     smallint,
            @status     smallint,
            @dbname     sysname

    -- CHECK PERMISSIONS --
    if (not is_member('db_accessadmin') = 1) and
       (not is_member('db_owner') = 1)
	begin
		raiserror(15247,-1,-1)
		return (1)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_addalias')
		return (1)
	end

    -- PREVENT USE OF CERTAIN LOGINS --
	if @loginame = 'sa'
	begin
		raiserror(15405, -1, -1, @loginame)
		return (1)
	end

    -- VALIDATE LOGIN NAME (OBTAIN SID) --
    select @status = CASE WHEN charindex('\', @loginame) > 0 THEN 12 ELSE 0 END
    if @status = 0
        select @sid = suser_sid(@loginame)          -- sql user
    -- retry sql user as nt with dflt domain
    if @sid is null
    begin
        select @sid = get_sid('\U'+@loginame, NULL) -- nt user
        if @sid is null
        begin
            if @status = 0
                raiserror(15007,-1,-1,@loginame)
            else
                raiserror(15401,-1,-1,@loginame)
            return (1)
        end
        select @status = 12
    end

    -- VALIDATE NAME-IN-DB (OBTAIN TARGET UID) --
    select @targuid = uid from sysusers where name = @name_in_db
                        and (issqluser = 1 or isntuser = 1)
						and uid NOT IN (3,4)	-- INFORMATION_SCHEMA, system_function_schema
    if @targuid is null
	begin
		raiserror(15008,-1,-1,@name_in_db)
		return (1)
	end

    -- ERROR IF LOGIN ALREADY IN DATABASE --
    if exists (select sid from sysusers where sid = @sid)
    begin

        -- ERROR IF ALREADY ALIASED --
        if exists (select sid from sysusers where sid = @sid and isaliased = 1)
	    begin
		    raiserror(15022,-1,-1)
		    return (1)
	    end

        -- ERROR: LOGIN ALREADY A USER --
        select @name_in_db = name, @dbname = db_name() from sysusers where sid = @sid
        raiserror(15278,-1,-1,@loginame,@name_in_db,@dbname)
        return (1)
    end

    -- ALTER NAME TO AVOID CONFLICTS IN NAME SPACE --
    select @loginame = '\' + @loginame
    if user_id(@loginame) is not null
    begin
	    raiserror(15023,-1,-1,@loginame)
        return (1)
    end

    -- OBTAIN NEW UID (RESERVE 1-4) --
    if user_name(5) IS NULL
        select @newuid = 5
    else
		select @newuid = min(uid)+1 from sysusers
            where uid >= 5 and uid < (16384 - 1)    -- stay in users range
                and user_name(uid+1) is null        -- uid not in use
    if @newuid is null
	begin
		raiserror(15065,-1,-1)
		return (1)
	end

    -- INSERT SYSUSERS ROW --
    insert into sysusers select
        @newuid, @status | 16, @loginame, @sid, 0x00,
                getdate(), getdate(), @targuid, NULL

    -- FINALIZATION: PRINT/RETURN SUCCESS --
    if @@error <> 0
        return (1)
    raiserror(15340,-1,-1)
    return (0) -- sp_addalias
go

------------------------------- sp_dropalias ----------------------------------

raiserror(15339,-1,-1,'sp_dropalias')
go
create procedure sp_dropalias
    @loginame   sysname     -- login who is currently aliased
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @sid        varbinary(85)

    -- CHECK PERMISSIONS --
    if (not is_member('db_accessadmin') = 1) and
       (not is_member('db_owner') = 1)
	begin
		raiserror(15247,-1,-1)
		return (1)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_dropalias')
		return (1)
	end

	-- VALIDATE LOGIN NAME (OBTAIN SID) --
	if charindex('\', @loginame) = 0
		select @sid = suser_sid(@loginame)          -- sql user
	if @sid is null
	begin
		select @sid = get_sid('\U'+@loginame, NULL) -- nt user
		if @sid is null
			begin
			-- Check directly for alias in sysusers
			SELECT @sid = sid FROM sysusers WHERE isaliased = 1 AND name = '\'+@loginame
			if @sid is null
			begin
				if charindex('\', @loginame) = 0
				raiserror(15007,-1,-1,@loginame)
				else
				raiserror(15401,-1,-1,@loginame)
				return (1)
			end
		end
	end

    -- DELETE THE ALIAS (IF ANY) --
    delete from sysusers where sid = @sid and isaliased = 1

    -- ERROR IF NO ROW DELETED --
    if @@rowcount = 0
    begin
		raiserror(15134,-1,-1)
		return (1)
    end

    -- FINALIZATION: PRINT/RETURN SUCCESS --
	raiserror(15492,-1,-1)
	return (0) -- sp_dropalias
go

--------------------------------- sp_addrole ----------------------------------

raiserror(15339,-1,-1,'sp_addrole')
go
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

    -- 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
go

-- FOR BACKWARD COMPATIBLIITY --
raiserror(15339,-1,-1,'sp_addgroup')
go
create procedure sp_addgroup
    @grpname   sysname         -- name of new role
as
    declare @ret int
    execute @ret = sp_addrole @grpname
    return @ret
go

-------------------------------- sp_droprole ----------------------------------

raiserror(15339,-1,-1,'sp_droprole')
go
create procedure sp_droprole
    @rolename       sysname     -- role to be dropped
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @uid        smallint,
            @owner      sysname,
            @ret        int

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	if (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_droprole')
		return (1)
	end

    -- ROLE NAME (OBTAIN OWNER FOR PERMISSIONS) --
    select @uid = uid, @owner = user_name(altuid) from sysusers
            where name = @rolename and issqlrole = 1

    -- CHECK PERMISSIONS --
    if (not is_member('db_securityadmin') = 1) and
       (not is_member('db_owner') = 1) and
       (@owner is NULL or not is_member(@owner) = 1)
    begin
		dbcc auditevent (111, 2, 0, NULL, NULL, @rolename, NULL)
		raiserror(15247,-1,-1)
		return (1)
	end
	else
	begin
		dbcc auditevent (111, 2, 1, NULL, NULL, @rolename, NULL)
	end

    -- ERROR IF ROLE NOT FOUND --
    if @uid is null
    begin
	    raiserror(15014,-1,-1,@rolename)
	    return (1)
    end

    -- ERROR IF FIXED ROLE or PUBLIC ROLE--
    if @uid < 16400	or @uid = 0
    begin
	    raiserror(15142,-1,-1, @rolename)
	    return (1)
    end

    -- ERROR IF ANYONE IS MEMBER OF ROLE (DISPLAYS MEMBERS) --
    if exists (select * from sysmembers where groupuid = @uid)
    begin
    	raiserror(15144,-1,-1)
    	select name = user_name(memberuid) from sysmembers where groupuid = @uid
    	return (1)
    end

    -- CHECK IF ROLE OWNS ANYTHING --
    execute @ret = sp_MScheck_uid_owns_anything @uid
    if @ret <> 0
        return (1)

    -- DROP SYSUSERS AND PROTECTION ENTRIES --
    delete from syspermissions where grantee = @uid
    delete from sysusers where uid = @uid

    -- FINALIZATION: PRINT/RETURN SUCCESS --
    if @@error <> 0
        return (1)
    raiserror(15493,-1,-1)
    return (0) -- sp_droprole
go


-- FOR BACKWARD COMPATIBLIITY --
raiserror(15339,-1,-1,'sp_dropgroup')
go
create procedure sp_dropgroup
    @rolename   sysname         -- name of role to drop
as
    declare @ret int
    execute @ret = sp_droprole @rolename
    return @ret
go


checkpoint
go

------------------------------- sp_addapprole ---------------------------------

raiserror(15339,-1,-1,'sp_addapprole')
go
create procedure sp_addapprole
    @rolename   sysname,        -- name of new app role
    @password   sysname         -- password for app role
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret        int,    -- return value of sp call
            @uid        smallint

	-- CHECK FOR NULL PASSWORD
	if (@password is null)
	begin
		raiserror(15034,-1,-1)
		return (1)
	end

    -- CHECK PERMISSIONS --
    if (not is_member('db_securityadmin') = 1) and
       (not is_member('db_owner') = 1)
	begin
		raiserror(15247,-1,-1)
		return (1)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	if (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_addapprole')
		return (1)
	end

    -- VALIDATE APPROLE NAME --
	execute @ret = sp_validname @rolename
	if @ret <> 0
		return (1)
	if (charindex('\', @rolename) > 0)
    begin
        raiserror(15006,-1,-1,@rolename)
        return (1)
    end

    -- ERROR IF SYSUSERS NAME ALREADY EXISTS --
    if user_id(@rolename) is not null OR
		@rolename IN ('system_function_schema','INFORMATION_SCHEMA')
    begin
        raiserror(15363,-1,-1,@rolename)
        return (1)
    end

    -- OBTAIN NEW APPROLE UID (RESERVE 1-4) --
    if user_name(5) IS NULL
        select @uid = 5
    else
		select @uid = min(uid)+1 from sysusers
            where uid >= 5 and uid < (16384 - 1)    -- stay in users 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, 32, @rolename, NULL, 0x00, getdate(),
                    getdate(), 1, convert(varbinary(256), pwdencrypt(@password)))

    -- FINALIZATION: PRINT/RETURN SUCCESS --
    if @@error <> 0
        return (1)
    raiserror(15425,-1,-1)
    return (0) -- sp_addapprole
go


----------------------- sp_approlepassword -------------------------------------------

raiserror(15339,-1,-1,'sp_approlepassword')
go
CREATE PROCEDURE sp_approlepassword
	@rolename		sysname,			-- name of app role
	@newpwd			sysname				-- new password
AS
	declare @roluid		smallint

	-- CHECK FOR NULL PASSWORD
	if (@newpwd is null)
	begin
		raiserror(15034,-1,-1)
		return (1)
	end

    -- CHECK PERMISSIONS --
    if (not is_member('db_securityadmin') = 1) and
       (not is_member('db_owner') = 1)
	begin
		dbcc auditevent (112, 1, 0, NULL, NULL, @rolename, NULL)
		raiserror(15247,-1,-1)
		return (1)
	end
	else
	begin
		dbcc auditevent (112, 1, 1, NULL, NULL, @rolename, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	if (@@trancount > 0)
	begin
		raiserror(15002, -1, -1, 'sp_approlepassword')
		return (1)
	end

    -- ROLE UID (OBTAIN OWNER FOR PERMISSIONS) --
	-- @pwd will be encrypted.
    select @roluid = uid from sysusers
                where name = @rolename and isapprole = 1

    -- ERROR IF APP ROLE NOT FOUND --
    if @roluid is null
    begin
	    raiserror(15014, -1, -1, @rolename)
	    return (1)
    end

	-- CHANGE PASSWORD --
	update sysusers set password = convert(varbinary(256), pwdencrypt(@newpwd)), updatedate = getdate()
			where uid = @roluid
	raiserror(15423,-1,-1,@rolename)

	return (0) -- sp_approlepassword
go


------------------------------- sp_setapprole ---------------------------------

raiserror(15339,-1,-1,'sp_setapprole')
go
create procedure sp_setapprole
    @rolename   sysname,        -- name app role
    @password   sysname,		-- password for app role
	@encrypt	varchar(10)	= 'none'	-- Encryption style ('none' | 'odbc')
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	if (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_setapprole')
		return (1)
	end

	-- CHECK PARAMETER
	if (@rolename IS NULL)
    begin
        raiserror(15431,-1,-1)
        return (1)
    end

	-- VALIDATE ENCRYPTION
	declare @encrStyle int
	select @encrStyle = case lower(@encrypt) when 'none' then 0 when 'odbc' then 1 else null end
	if @encrStyle is null
	begin
        raiserror(15600,-1,-1,'sp_setapprole')
        return (1)
	end

    -- SP MUST BE CALLED AT ADHOC LEVEL --
    if (@@nestlevel > 1)
    begin
        raiserror(15422,-1,-1)
        return (1)
    end

    -- ACTIVATE APPROLE (THIS IS ONLY VALID FROM THIS SP!) --
    setuser @rolename, @password, @encrStyle

    -- RETURN SUCCESS/FAILURE --
    if (@@error <> 0)
        return (1)

	raiserror(15494,-1,-1,@rolename)

    return (0) -- sp_setapprole
go

------------------------------ sp_dropapprole ---------------------------------

raiserror(15339,-1,-1,'sp_dropapprole')
go
create procedure sp_dropapprole
    @rolename       sysname     -- role to be dropped
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @uid        smallint,
            @ret        int

    -- CHECK PERMISSIONS --
    if (not is_member('db_securityadmin') = 1) and
       (not is_member('db_owner') = 1)
	begin
		raiserror(15247,-1,-1)
		return (1)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	if (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_dropapprole')
		return (1)
	end

    -- ERROR IF ROLE NOT FOUND --
    select @uid = uid from sysusers where name = @rolename and isapprole = 1
    if @uid is null
    begin
	    raiserror(15014,-1,-1,@rolename)
	    return (1)
    end

    -- CHECK IF ROLE OWNS ANYTHING --
    execute @ret = sp_MScheck_uid_owns_anything @uid
    if @ret <> 0
        return (1)

    -- DROP SYSUSERS AND PROTECTION ENTRIES --
    delete from syspermissions where grantee = @uid
    delete from sysusers where uid = @uid

    -- FINALIZATION: PRINT/RETURN SUCCESS --
    if @@error <> 0
        return (1)
    raiserror(15495,-1,-1)
    return (0) -- sp_dropapprole
go

------------------------------ sp_addrolemember -------------------------------

raiserror(15339,-1,-1,'sp_addrolemember')
go
CREATE PROCEDURE sp_addrolemember
	@rolename       sysname,
	@membername     sysname
AS
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @roluid     smallint,
            @owner      smallint,
            @memuid     smallint,
            @ret        int
    declare @ruidbyte   smallint,
            @ruidbit    smallint
	declare @proc		nvarchar(50)

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	if (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_addrolemember')
		return (1)
	end

	--cannot change membership of public
	if @rolename = 'public'
	begin
		raiserror(15081, -1,-1)
		return(1)
	end

    -- ROLE NAME (OBTAIN OWNER FOR PERMISSIONS) --
    select @roluid = uid, @owner = altuid from sysusers
                where name = @rolename and issqlrole = 1

    -- ERROR IF ROLE NOT FOUND OR PUBLIC --
    if @roluid is null
    begin
	    raiserror(15014,-1,-1,@rolename)
	    return (1)
    end

    -- CHECK PERMISSIONS --
	-- Only member of db_owner can add members to db-fixed roles --
    if (not is_member('db_owner') = 1) and
       (not (@roluid < 16400 and is_member('db_owner') = 1)) and
       (not (@roluid >= 16400 and is_member('db_securityadmin') = 1)) and
       (not (@roluid >= 16400 and is_member(user_name(@owner)) = 1))
    begin
		dbcc auditevent (110, 1, 0, NULL, @membername, @rolename, NULL)
		raiserror(15247,-1,-1)
		return (1)
	end
	else
	begin
		dbcc auditevent (110, 1, 1, NULL, @membername, @rolename, NULL)
	end

    -- CHECK MEMBER NAME (ATTEMPT ADDING IMPLICIT ROW FOR NT NAME) --
    select @memuid = uid from sysusers where name = @membername and isaliased = 0
    if @memuid is null
    begin
        execute @ret = sp_MSadduser_implicit_ntlogin @membername
        select @memuid = uid from sysusers where name = @membername and isaliased = 0
    end
    if @memuid is null
    begin
		raiserror(15410, -1, -1, @membername)
		return (1)
    end

    -- CANNOT CHANGE MEMBERSHIP OF FIXED ROLES OR DBO --
    if @memuid in (1,0,3,4) --dbo, public, INFORMATION_SCHEMA, system_function_schema
		or (@memuid >= 16384 and @memuid < 16400)
    begin
        raiserror(15405, -1 ,-1, @membername)
        return (1)
    end

    -- CHECK FOR CIRCULAR MEMBERSHIPS --
    if is_userinrole(@rolename, @membername) = 1
    begin
		raiserror(15413, -1, -1)
		return (1)
    end

    -- SET ROLE BIT FOR THIS USER
    select @ruidbyte = ((@roluid - 16384) / 8) + 1
         , @ruidbit = power(2, @roluid & 7)
    update sysusers set roles = convert(varbinary(2048),
				substring(convert(binary(2048), roles), 1, @ruidbyte-1)
				+ convert(binary(1), (@ruidbit) | substring(convert(binary(2048), roles), @ruidbyte, 1))
				+ substring(convert(binary(2048), roles), @ruidbyte+1, 2048-@ruidbyte) ),
            updatedate = getdate()
        where uid = @memuid
    -- END ROLE BIT MANIPULATION

    -- INVALIDATE CACHED PERMISSIONS (MEMBERSHIP CHANGES PERMISSIONS) --
    select @ret = @@error   -- save success state
    grant all to null

    -- FINALIZATION: PRINT/RETURN SUCCESS --
    if @@error <> 0 or @ret <> 0
        return (1)

	raiserror(15488,-1,-1,@membername,@rolename)

    return (0) -- sp_addrolemember
go

checkpoint
go

----------------------------- sp_droprolemember -------------------------------

raiserror(15339,-1,-1,'sp_droprolemember')
go
CREATE PROCEDURE sp_droprolemember
	@rolename       sysname,
	@membername     sysname
AS
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @roluid     smallint,
            @owner      smallint,
            @memuid     smallint,
            @ret        int
    declare @ruidbyte   smallint,
            @ruidbit    smallint
	declare @proc		nvarchar(50)

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	if (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_droprolemember')
		return (1)
	end

	--cannot change membership of public
	if @rolename = 'public'
	begin
		raiserror(15081, -1,-1)
		return(1)
	end

	    -- ROLE NAME (OBTAIN OWNER FOR PERMISSIONS) --
    select @roluid = uid, @owner = altuid from sysusers
                where name = @rolename and issqlrole = 1

    -- ERROR IF ROLE NOT FOUND OR PUBLIC --
    if @roluid is null
    begin
	    raiserror(15409,-1,-1,@rolename)
	    return (1)
    end

    -- CHECK PERMISSIONS --
	-- Only member of db_owner can drop members from db-fixed roles --
    if (not is_member('db_owner') = 1) and
       (not (@roluid < 16400 and is_member('db_owner') = 1)) and
       (not (@roluid >= 16400 and is_member('db_securityadmin') = 1)) and
       (not (@roluid >= 16400 and is_member(user_name(@owner)) = 1))
    begin
		dbcc auditevent (110, 2, 0, NULL, @membername, @rolename, NULL)
		raiserror(15247,-1,-1)
		return (1)
	end
	else
	begin
		dbcc auditevent (110, 2, 1, NULL, @membername, @rolename, NULL)
	end

    -- ERROR IF MEMBER NAME NOT NULL AND NOT FOUND --
    select @memuid = uid from sysusers where name = @membername and isaliased = 0
    if @memuid is null
    begin
		raiserror(15410, -1, -1, @membername)
		return (1)
    end

    -- CANNOT CHANGE MEMBERSHIP OF FIXED ROLES OR DBO --
    if @membername in ('dbo','public') or (@memuid >= 16384 and @memuid < 16400)
    begin
        raiserror(15405, -1 ,-1, @membername)
        return (1)
    end

    -- CLEAR ROLE BIT FOR THIS USER
    select @ruidbyte = ((@roluid - 16384) / 8) + 1
         , @ruidbit = power(2, @roluid & 7)
    update sysusers set roles = convert(varbinary(2048),
				substring(convert(binary(2048), roles), 1, @ruidbyte-1)
				+ convert(binary(1), (~@ruidbit) & substring(convert(binary(2048), roles), @ruidbyte, 1))
				+ substring(convert(binary(2048), roles), @ruidbyte+1, 2048-@ruidbyte) ),
            updatedate = getdate()
        where uid = @memuid
    -- END ROLE BIT MANIPULATION

    -- INVALIDATE CACHED PERMISSIONS (MEMBERSHIP CHANGES PERMISSIONS) --
    select @ret = @@error   -- save success state
    grant all to null

    -- FINALIZATION: PRINT/RETURN SUCCESS --
    if @@error <> 0 or @ret <> 0
        return (1)

	raiserror(15489,-1,-1,@membername,@rolename)

    return (0) -- sp_droprolemember
go

------------------------------- sp_changegroup --------------------------------

raiserror(15339,-1,-1,'sp_changegroup')
go
create procedure sp_changegroup
    @grpname    sysname,    -- name of new role
    @username   sysname     -- user to switch
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @uid        smallint,
			@ruid        smallint,
            @cnt        int,
            @ret        int,
            @oldgrp     sysname

     select @ruid = uid from sysusers
                where name = @grpname and issqlrole = 1

    -- ERROR IF GROUP NOT FOUND --
    if @ruid is null
    begin
	    raiserror(15014,-1,-1,@grpname)
	    return (1)
    end

    -- LIMIT TO USERS WITH ACCESS (BACKWARD COMPAT ONLY!) --
    select @uid = uid from sysusers where name = @username
                and (issqluser = 1 or isntuser = 1) and hasdbaccess = 1
    if @uid is null
	begin
		raiserror(15008,-1,-1,@username)
		return (1)
	end

    -- ONLY VALID IF USER IS MEMBER OF NO MORE THAN ONE GROUP --
    select @cnt = count(*) from sysmembers where memberuid = @uid
    if @cnt > 1
    begin
	    raiserror(15415, -1, -1)
	    return (1)
    end

	-- AUDIT SUCCESSFUL SECURITY CHECK --
	dbcc auditevent (110, 3, 1, NULL, @username, @grpname, NULL)

    -- REMOVE MEMBERSHIP IF NEEDED --
	if (@cnt = 1)
	begin
        select @oldgrp = user_name(groupuid) from sysmembers where memberuid = @uid
        execute @ret = sp_droprolemember @oldgrp, @username
        if @ret <> 0
            return (1)
	end

    -- ADD MEMBERSHIP --
    if (@grpname <> 'public')
    begin
        execute @ret = sp_addrolemember @grpname, @username
        if @ret <> 0
            return (1)
    end

    -- FINALIZATION: RETURN SUCCESS --
    raiserror(15496,-1,-1)

	return (0) -- sp_changegroup
go

---------------------------- sp_change_users_login ----------------------------

raiserror(15339,-1,-1,'sp_change_users_login')
go
CREATE PROCEDURE sp_change_users_login
    @Action               varchar(10)       -- REPORT / UPDATE_ONE / AUTO_FIX
   ,@UserNamePattern      sysname  = Null
   ,@LoginName            sysname  = Null
AS
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @exec_stmt nvarchar(430)

	declare @ret            int,
            @FixMode        char(5),
            @cfixesupdate   int,        -- count of fixes by update
            @cfixesaddlogin int,        -- count of fixes by sp_addlogin
            @dbname         sysname,
            @loginsid       varbinary(85),
            @110name        sysname

    -- SET INITIAL VALUES --
    select  @dbname         = db_name(),
            @cfixesupdate   = 0,
            @cfixesaddlogin = 0

    -- ERROR IF IN USER TRANSACTION --
    if @@trancount > 0
    begin
        raiserror(15289,-1,-1)
        return (1)
    end

    -- INVALIDATE USE OF SPECIAL LOGIN/USER NAMES --
    if @LoginName = 'sa'
    begin
        raiserror(15287,-1,-1,@LoginName)
        return (1)
    end
    if user_id(@UserNamePattern) in (1,0,3,4) --dbo, public, INFORMATION_SCHEMA, system_function_schema
    begin
        raiserror(15287,-1,-1,@UserNamePattern)
        return (1)
    end

    -- HANDLE REPORT --
    if upper(@Action) = 'REPORT'
    begin

        -- VALIDATE PARAMS --
        if @UserNamePattern IS NOT Null or @LoginName IS NOT Null
        begin
            raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
            return (1)
        end

        -- GENERATE REPORT --
        select UserName = name, UserSID = sid from sysusers
            where issqluser = 1 and (sid is not null and sid <> 0x0)
                    and suser_sname(sid) is null
            order by name
        return (0)
    end

    -- HANDLE UPDATE_ONE --
    if upper(@Action) = 'UPDATE_ONE'
    begin

        -- CHECK PERMISSIONS --
        if not is_member('db_owner') = 1
        begin
            raiserror(15247,-1,-1)
            return (1)
        end

        -- ERROR IF PARAMS NULL --
        if @UserNamePattern IS Null or @LoginName IS Null
        begin
            raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
            return (1)
        end

        -- VALIDATE PARAMS --
        -- Can ONLY remap SQL Users to SQL Logins!  Should be no need
        --  for re-mapping NT logins, and if you try, you'll mess up
        --  the user status bits! 
        if not exists (select name from sysusers where
                name = @UserNamePattern             -- match user name
            and issqluser = 1)                      -- must be sql user
        begin
            raiserror(15291,-1,-1,'User',@UserNamePattern)
            return (1)
        end
        select @loginsid = sid from master.dbo.syslogins where
                loginname = @LoginName              -- match login name
            and isntname = 0                        -- cannot use nt logins
        if @loginsid is null
        begin
            raiserror(15291,-1,-1,'Login',@LoginName)
            return (1)
        end

        -- ERROR IF SID ALREADY IN USE IN DATABASE --
        if exists (select sid from sysusers where sid = @loginsid
                    and name <> @UserNamePattern)
        begin
		    raiserror(15063,-1,-1)
		    return (1)
        end

        -- CHANGE THE USERS LOGIN (SID) --
        update sysusers set sid = @loginsid, updatedate = getdate()
                where name = @UserNamePattern and issqluser = 1
                and sid <> @loginsid

        -- FINALIZATION: REPORT (ONLY IF NOT SUCCESSFUL) AND EXIT --
        if @@error <> 0 or @@rowcount <> 1
            raiserror(15295,-1,-1, 0)
        return (0)
    end

    -- ERROR IF NOT AUTO_FIX --
    if upper(@Action) <> 'AUTO_FIX'
    begin
        raiserror(15286,-1,-1,@Action)
        return (1)
    end

    -- HANDLE AUTO_FIX --
    -- CHECK PERMISSIONS --
    if not is_srvrolemember('sysadmin') = 1
    begin
        raiserror(15247,-1,-1)
        return (1)
    end

    -- VALIDATE PARAMS --
    if @UserNamePattern IS Null or @LoginName IS NOT Null
    begin
        raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
        return (1)
    end

    -- LOOP THRU ORPHANED USERS --
	select @exec_stmt = 'DECLARE ms_crs_110_Users cursor global for
            select name from sysusers
            where name = N' + quotename( @UserNamePattern , '''')+ '
                and issqluser = 1 and suser_sname(sid) is null'
    EXECUTE (@exec_stmt)
    OPEN ms_crs_110_Users

    WHILE (110=110)
    begin
        FETCH next from ms_crs_110_Users into @110name
        if (@@fetch_status <> 0)
        begin
            DEALLOCATE ms_crs_110_Users
            BREAK
        end

        -- IS NAME ALREADY IN USE? --
        -- if suser_sid(@110name) is null
		if not exists(select * from master.dbo.syslogins where loginname = @110name)
        begin

            -- ADD LOGIN --
            execute @ret = sp_addlogin @110name, Null, @dbname
            if @ret <> 0 or suser_sid(@110name) is null
            begin
                raiserror(15497,16,1,@110name)
                deallocate ms_crs_110_Users
                return (1)
            end
            select @FixMode = '1AddL'
            raiserror(15293,-1,-1,@110name)
        end
        ELSE
        begin
            Select @FixMode = '2UpdU'
            Raiserror(15292,-1,-1,@110name)
        end

        -- REPORT ERROR & CONTINUE IF DUPLICATE SID IN DB --
        select @loginsid = suser_sid(@110name)
        if user_sid(@loginsid) is not null
        begin
            raiserror(15331,-1,-1,@110name)
            CONTINUE
        end

        -- UPDATE SYSUSERS ROW --
        update sysusers set sid = @loginsid, updatedate = getdate() where name = @110name
        if @@error <> 0
        begin
            raiserror(15498,17,127)
            deallocate ms_crs_110_Users
            return (1)
        end


        if @FixMode = '1AddL'
            Select @cfixesaddlogin = @cfixesaddlogin + 1
        else
            Select @cfixesupdate = @cfixesupdate + 1
    end -- loop 110

    -- REPORT AND RETURN SUCCESS --
    raiserror(15295,-1,-1,@cfixesupdate)
    raiserror(15294,-1,-1,@cfixesaddlogin)
    return (0) -- sp_change_users_login
go

------------------------------ sp_changedbowner -------------------------------

raiserror(15339,-1,-1,'sp_changedbowner')
go
create procedure sp_changedbowner
    @loginame       sysname,		-- login to become dbo
    @map            varchar(5) = NULL	-- True to map aliases, else drop
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret        int,
            @newsid     varbinary(85),
            @status     smallint

    -- CHECK PERMISSIONS (Note: All sysadmins are dbo) --
    if is_member('db_owner') = 0
    begin
        raiserror(15247,-1,-1)
        return(1)
    end

    -- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
    if db_name() in ('master', 'model', 'tempdb')
    begin
        raiserror(15109,-1,-1)
        return(1)
    end

    -- CHECK LOGIN NAME IS VALID (NT/SQL USER ONLY!) --
    select @newsid = sid, @status = 2 from master.dbo.syslogins
                    where loginname = @loginame and isntname = 0
    if @newsid is null
        select @status = 14, @newsid = get_sid('\U'+@loginame, NULL)
    if @newsid is null
    begin
        raiserror(15007,-1,-1,@loginame)
        return (1)
    end

    -- CHECK IF LOGIN ALREADY ALIASED IN DB --
    if exists (select sid from sysusers where isaliased = 1 and sid = @newsid)
    begin
        raiserror(15111,-1,-1)
        return (1)
    end

    -- CHECK IF LOGIN ALREADY KNOWN TO DATABASE --
    if exists (select sid from sysusers where sid = @newsid)
    begin
        raiserror(15110,-1,-1)
        return (1)
    end

    -- MAKE THE FOLLOWING REMOVE/REMAP/DELETES ATOMIC --
    begin transaction

    -- REMAP DBO TO NEW SID --
    update sysusers set sid = @newsid, status = @status, updatedate = getdate()
            where name = 'dbo'

    -- REMOVE OTHER DBO-ALIASES IF REMAPPING NOT REQUESTED --
    if lower(@map) <> 'true'
    begin
        delete from sysusers where isaliased = 1 and altuid = user_id('dbo')
        raiserror(15500,-1,-1)
    end
    else
        raiserror(15499,-1,-1)     -- nothing to do to <remap>

    -- REFLECT NEW OWNER IN SYSDATABASES --
    update master.dbo.sysdatabases set sid = @newsid where dbid = db_id()
    commit transaction

    -- CHECKPOINT DATABASE TO FORCE CHANGES TO IN-MEMORY STRUCTURE --
    checkpoint
    raiserror(15501,-1,-1)
    return (0) -- sp_changedbowner
go

------------------------- sp_check_removable_sysusers -------------------------

raiserror(15339,-1,-1,'sp_check_removable_sysusers')
go
-----------------------------------------------------
-- NOTE: FOR INTERNAL USE ONLY (sp_certify_removable)
--      DO NOT DOCUMENT OR USE!
-----------------------------------------------------
create procedure sp_check_removable_sysusers
    @autofix    varchar(4)      -- true or other
as
    -- CHECK FOR DATABASE OWNED BY SQL USER --
    if exists (select name from sysusers where name = 'dbo' and issqluser = 1 and sid <> suser_sid('sa'))
    begin
        if @autofix <> 'auto'
        begin
			raiserror(15258,-1,-1)
			return(1)
        end

        -- MAKE SA THE DBO --
		raiserror(15502,-1,-1)
        update sysusers set sid = suser_sid('sa'), status = 2, updatedate = getdate()
                where name = 'dbo'
    end

    -- CHECK FOR PERMISSIONS GRANTED TO or BY SQL USERS --
    if exists (select grantee from syspermissions where grantee in
                (select uid from sysusers u where issqluser = 1 and u.uid > 4))
	OR exists (select grantor from syspermissions where grantor in
                (select uid from sysusers u where issqluser = 1 and u.uid > 4))
    begin
        if @autofix = 'auto'
            PRINT 'CANNOT AUTO-AUTOFIX GRANT-WITH-GRANT CHAINS'
		raiserror(15053,-1,-1)
		return(1)
    end

    -- CHECK FOR OBJECTS OWNED BY SQL USERS --
    if exists (select uid from sysobjects where uid in
                (select uid from sysusers u where issqluser = 1 and u.uid > 4))
    begin
        if @autofix <> 'auto'
        begin
			raiserror(15053,-1,-1)
			return(1)
        end

        -- ASSIGN DBO AS OWNER OF OTHER OBJECTS (MAY FAIL WITH DUPL!) --
        raiserror(15503,-1,-1)
        update sysobjects set uid = 1 where uid in
                (select uid from sysusers u where issqluser = 1 and u.uid > 4)
        if @@error <> 0
            return (1)
    end

    -- CHECK FOR TYPES OWNED BY SQL USERS --
    if exists (select uid from systypes where uid in
                (select uid from sysusers u where issqluser = 1 and u.uid > 4))
    begin
        if @autofix <> 'auto'
        begin
			raiserror(15053,-1,-1)
			return(1)
        end

        -- ASSIGN DBO AS OWNER OF TYPES --
        raiserror(15503,-1,-1)
        update systypes set uid = 1 where uid in
                (select uid from sysusers u where issqluser = 1 and u.uid > 4)
    end

    -- CHECK FOR ROLES OWNED BY SQL USERS --
    if exists (select altuid from sysusers where (issqlrole = 1 or isapprole = 1) and
        altuid in (select uid from sysusers u where u.issqluser = 1 and u.uid > 4))
    begin
        if @autofix <> 'auto'
        begin
			raiserror(15053,-1,-1)
			return(1)
        end

        -- ASSIGN DBO AS OWNER OF TYPES --
        raiserror(15503,-1,-1)
        update sysusers set altuid = 1, updatedate = getdate()
            where (issqlrole = 1 or isapprole = 1) and
            altuid in (select uid from sysusers u where u.issqluser = 1 and u.uid > 4)
    end

    -- CHECK FOR SQL LOGINS AS USERS --
    if exists (select uid from sysusers where issqluser = 1 and uid > 4)
    begin
        if @autofix <> 'auto'
        begin
			raiserror(15254,-1,-1)
			return(1)
        end

        -- DELETE SQL USERS AND DEPENDENT ALIASES --
        raiserror(15504,-1,-1)
        delete from sysusers where issqluser = 1 and uid > 4
        delete from sysusers where isaliased = 1 and user_name(altuid) is null
    end

    -- CHECK FOR SQL LOGINS ALIASED --
    if exists (select uid from sysusers where isaliased = 1 and isntname = 0)
    begin
        if @autofix <> 'auto'
        begin
			raiserror(15254,-1,-1)
			return(1)
        end

        -- DELETE ALIASED SQL USERS --
        raiserror(15504,-1,-1)
        delete from sysusers where isaliased = 1 and isntname = 0
    end

	-- Success
	return 0
go

checkpoint
go

---------------------------- sp_changeobjectowner -----------------------------

raiserror(15339,-1,-1,'sp_changeobjectowner')
go
create procedure sp_changeobjectowner
	@objname	nvarchar(517),		-- may be "[owner].[object]"
	@newowner	sysname				-- must be entry from sysusers
as
	Set nocount      on
	Set ansi_padding on
	declare	@objid		int,
			@newuid		smallint

	-- CHECK PERMISSIONS: Because changing owner changes both schema and
	--	permissions, the caller must be one of:
	-- (1) db_owner
	-- (2) db_ddladmin AND db_securityadmin
    if (is_member('db_owner') = 0) and
		(is_member('db_securityadmin') = 0 OR is_member('db_ddladmin') = 0)
    begin
		raiserror(15247,-1,-1)
		return (1)
    end

	-- RESOLVE OBJECT NAME (CANNOT BE A CHILD OBJECT: TRIGGER/CONSTRAINT) --
	select @objid = object_id(@objname, 'local')
	if (@objid is null) OR
		(select parent_obj from sysobjects where id = @objid) <> 0 OR
		ObjectProperty(@objid, 'IsMSShipped') = 1 OR
		ObjectProperty(@objid, 'IsSystemTable') = 1 OR
		ObjectProperty(@objid, 'ownerid') in (0,3,4) OR --public, INFORMATION_SCHEMA, system_function_schema
		-- Check for Dependencies: No RENAME or CHANGEOWNER of OBJECT when exists:
		EXISTS (SELECT * FROM sysdepends d WHERE
			d.depid = @objid		-- A dependency on this object
			AND d.deptype > 0		-- that is enforced
			AND @objid <> d.id		-- that isn't a self-reference (self-references don't use object name)
			AND @objid <>			-- And isn't a reference from a child object (also don't use object name)
				(SELECT o.parent_obj FROM sysobjects o WHERE o.id = d.id)
			)
	begin
		-- OBJECT NOT FOUND
		raiserror(15001,-1,-1,@objname)
		return 1
	end

	-- RESOLVE NEW OWNER NAME (ATTEMPT ADDING IMPLICIT ROW FOR NT NAME) --
    --  Disallow aliases, and public cannot own objects --
	select @newuid = uid from sysusers where name = @newowner
                            and isaliased = 0
							and uid not in (0,3,4) --public, INFORMATION_SCHEMA, system_function_schema
    if @newuid is null
    begin
        execute sp_MSadduser_implicit_ntlogin @newowner
        select @newuid = uid from sysusers where name = @newowner
                            and isaliased = 0 and name <> 'public'
    end
    if @newuid is null
    begin
		raiserror(15410, -1, -1, @newowner)
		return (1)
    end

	-- CHECK IF CHANGING OWNER OF OBJECT OR ITS CHILDREN WOULD PRODUCE A DUPLICATE
	if exists (select * from sysobjects where uid = @newuid and name in
		(select name from sysobjects where id = @objid OR parent_obj = @objid))
	begin
		raiserror(15505,-1,-1,@objname,@newowner)
		return (1)
	end


	-- DO THE OWNER TRANSFER (WITH A WARNING) --
	raiserror(15477,-1,-1)
	begin transaction
	-- Locks Object and increments schema_ver.
	DBCC LockObjectSchema(@objname)
	-- drop permissions (they'll be incorrect with new owner) --
	delete syspermissions where id = @objid
	update sysobjects set uid = @newuid where id = @objid
	update sysobjects set uid = @newuid where parent_obj = @objid
	commit transaction

	return 0	-- sp_changeobjectowner
go

-- GRANT PUBLIC ACCESS (SP'S DO INTERNAL PERMISSIONS CHECKS) --
grant execute on sp_grantdbaccess to public
grant execute on sp_revokedbaccess to public
grant execute on sp_adduser to public
grant execute on sp_dropuser to public
grant execute on sp_addalias to public
grant execute on sp_dropalias to public
grant execute on sp_addrole to public
grant execute on sp_droprole to public
grant execute on sp_dropgroup to public
grant execute on sp_addgroup to public
grant execute on sp_dropgroup to public
grant execute on sp_addapprole to public
grant execute on sp_approlepassword to public
grant execute on sp_setapprole to public
grant execute on sp_dropapprole to public
grant execute on sp_addrolemember to public
grant execute on sp_droprolemember to public
grant execute on sp_changegroup to public
grant execute on sp_change_users_login to public
grant execute on sp_changedbowner to public
grant execute on sp_changeobjectowner to public
go
/**********************  END DATABASE-ACCESS-SECURITY ************************/

/******************************************************************************
************************  SECURITY-HELP-PROCEDURES  ***************************
******************************************************************************/
checkpoint
go
if object_id('sp_helpsrvrole','P') IS NOT NULL
	drop procedure sp_helpsrvrole
if object_id('sp_srvrolepermission','P') IS NOT NULL
	drop procedure sp_srvrolepermission
if object_id('sp_helpsrvrolemember','P') IS NOT NULL
	drop procedure sp_helpsrvrolemember
if object_id('sp_helpdbfixedrole','P') IS NOT NULL
	drop procedure sp_helpdbfixedrole
if object_id('sp_dbfixedrolepermission','P') IS NOT NULL
	drop procedure sp_dbfixedrolepermission
if object_id('sp_helprolemember','P') IS NOT NULL
	drop procedure sp_helprolemember
if object_id('sp_helprole','P') IS NOT NULL
	drop procedure sp_helprole
if object_id('sp_helpntgroup','P') IS NOT NULL
	drop procedure sp_helpntgroup
if object_id('xp_logininfo','P') IS NOT NULL
	drop procedure xp_logininfo
go


----------------------- sp_helpsrvrole ----------------------------------------

raiserror(15339,-1,-1,'sp_helpsrvrole')
go
CREATE PROCEDURE sp_helpsrvrole
	@srvrolename		sysname = NULL
AS
	if @srvrolename is not null
	begin
		-- VALIDATE GIVEN NAME
		if not exists (select * from master.dbo.spt_values
				where name = @srvrolename and low = 0 and type = 'SRV')
		begin
			raiserror(15412, -1, -1, @srvrolename)
			return (1)
		end

		-- RESULT SET FOR SINGLE SERVER-ROLE
		select 'ServerRole' = v1.name, 'Description' = v2.name
			from master.dbo.spt_values v1, master.dbo.spt_values v2
			where v1.name = @srvrolename and
				  v1.low = 0 and
				  v1.type = 'SRV' and
				  v2.low = -1 and
				  v2.type = 'SRV' and
				  v1.number = v2.number
	end
	else
	begin
		-- RESULT SET FOR ALL SERVER-ROLES
		select 'ServerRole' = v1.name, 'Description' = v2.name
			from master.dbo.spt_values v1, master.dbo.spt_values v2
			where v1.low = 0 and
				  v1.type = 'SRV' and
				  v2.low = -1 and
				  v2.type = 'SRV' and
				  v1.number = v2.number
	end

    return (0) -- sp_helpsrvrole
go

----------------------- sp_srvrolepermission ----------------------------------

raiserror(15339,-1,-1,'sp_srvrolepermission')
go
CREATE PROCEDURE sp_srvrolepermission
	@srvrolename       sysname = NULL
AS
	if @srvrolename is not null
	begin
		-- VALIDATE GIVEN NAME
		if not exists (select * from master.dbo.spt_values
				where name = @srvrolename and low = 0 and type = 'SRV')
		begin
			raiserror(15412, -1, -1, @srvrolename)
			return (1)
		end

		-- RESULT SET FOR SINGLE SERVER-ROLE
		select distinct 'ServerRole' = v1.name, 'Permission' = v2.name
			from master.dbo.spt_values v1, master.dbo.spt_values v2
			where v1.name = @srvrolename and
				  v1.low = 0 and
				  v1.type = 'SRV' and
				  ((v2.type = 'SRV' and ((v1.number = 16 and v1.number <= v2.number) or (v1.number <> 16 and v1.number = v2.number))) or
				  (v2.type = 'DBR' and v1.number = 16 and not (v2.name like N'No %'))) and
				  v2.low > 0
			order by v1.name, v2.name
	end
	else
	begin
		-- RESULT SET FOR ALL SERVER-ROLES
		select distinct 'ServerRole' = v1.name, 'Permission' = v2.name
			from master.dbo.spt_values v1, master.dbo.spt_values v2
			where v1.low = 0 and
				  v1.type = 'SRV' and
				  ((v2.type = 'SRV' and ((v1.number = 16 and v1.number <= v2.number) or (v1.number <> 16 and v1.number = v2.number))) or
				  (v2.type = 'DBR' and v1.number = 16 and not (v2.name like N'No %'))) and
				  v2.low > 0
			order by v1.name, v2.name
	end

    return (0) -- sp_srvrolepermission
go


----------------------- sp_helpsrvrolemember ----------------------------------

raiserror(15339,-1,-1,'sp_helpsrvrolemember')
go
CREATE PROCEDURE sp_helpsrvrolemember
	@srvrolename       sysname = NULL
AS
	if @srvrolename is not null
	begin
		-- VALIDATE GIVEN NAME
		if not exists (select * from master.dbo.spt_values
				where name = @srvrolename and low = 0 and type = 'SRV')
		begin
			raiserror(15412, -1, -1, @srvrolename)
			return (1)
		end

		-- RESULT SET FOR SINGLE SERVER-ROLE
		select 'ServerRole' = spv.name, 'MemberName' = lgn.name, 'MemberSID' = lgn.sid
			from master.dbo.spt_values spv, master.dbo.sysxlogins lgn
			where spv.name = @srvrolename and
				  spv.low = 0 and
				  spv.type = 'SRV' and
				  lgn.srvid IS NULL and
				  spv.number & lgn.xstatus = spv.number
	end
	else
	begin
		-- RESULT SET FOR ALL SERVER-ROLES
		select 'ServerRole' = spv.name, 'MemberName' = lgn.name, 'MemberSID' = lgn.sid
			from master.dbo.spt_values spv, master.dbo.sysxlogins lgn
			where spv.low = 0 and
				  spv.type = 'SRV' and
				  lgn.srvid IS NULL and
				  spv.number & lgn.xstatus = spv.number
	end

    return (0) -- sp_helpsrvrolemember
go

----------------------- sp_helpdbfixedrole ----------------------------------------

raiserror(15339,-1,-1,'sp_helpdbfixedrole')
go
CREATE PROCEDURE sp_helpdbfixedrole
	@rolename		sysname = NULL
AS
	if @rolename is not null
	begin
		-- VALIDATE GIVEN NAME
		if not exists (select * from sysusers where name = @rolename
						and uid >= 16384 and uid <= 16393)
		begin
			raiserror(15412, -1, -1, @rolename)
			return (1)
		end

		-- RESULT SET FOR SINGLE FIXED-ROLE
		select 'DbFixedRole' = usr.name, 'Description' = spv.name
			from sysusers usr, master.dbo.spt_values spv
			where usr.name = @rolename and
				  usr.uid >= 16384 and
				  usr.uid <= 16393 and
				  usr.uid = spv.number and
				  spv.type = 'DBR' and
				  spv.low = -1
	end
	else
	begin
		-- RESULT SET FOR ALL FIXED-ROLES
		select 'DbFixedRole' = usr.name, 'Description' = spv.name
			from sysusers usr, master.dbo.spt_values spv
			where usr.uid >= 16384 and
				  usr.uid <= 16393 and
				  usr.uid = spv.number and
				  spv.type = 'DBR' and
				  spv.low = -1
	end

    return (0) -- sp_helpdbfixedrole
go


----------------------- sp_dbfixedrolepermission ------------------------------

raiserror(15339,-1,-1,'sp_dbfixedrolepermission')
go
CREATE PROCEDURE sp_dbfixedrolepermission
	@rolename       sysname = NULL
AS
	if @rolename is not null
	begin
		-- VALIDATE GIVEN NAME
		if not exists (select * from sysusers where name = @rolename
						and uid >= 16384 and uid <= 16393)
		begin
			raiserror(15412, -1, -1, @rolename)
			return (1)
		end

		-- RESULT SET FOR SINGLE FIXED-ROLE
		select DISTINCT 'DbFixedRole' = usr.name, 'Permission' = spv.name
			from sysusers usr, master.dbo.spt_values spv
			where usr.name = @rolename and
				  usr.uid >= 16384 and
				  usr.uid <= 16393 and
				  spv.type = 'DBR' and
				  ((usr.uid = 16384 and spv.number >= 16384 and spv.number < 16392) or (usr.uid <> 16384 and usr.uid = spv.number)) and
				  spv.low > 0
			order by usr.name, spv.name
	end
	else
	begin
		-- RESULT SET FOR ALL FIXED-ROLES
		select DISTINCT 'DbFixedRole' = usr.name, 'Permission' = spv.name
			from sysusers usr, master.dbo.spt_values spv
			where usr.uid >= 16384 and
				  usr.uid <= 16393 and
				  spv.type = 'DBR' and
				  ((usr.uid = 16384 and spv.number >= 16384 and spv.number < 16392) or (usr.uid <> 16384 and usr.uid = spv.number)) and
				  spv.low > 0
			order by usr.name, spv.name
	end

    return (0) -- sp_dbfixedrolepermission
go


----------------------- sp_helprolemember -------------------------------------

raiserror(15339,-1,-1,'sp_helprolemember')
go
CREATE PROCEDURE sp_helprolemember
	@rolename       sysname = NULL
AS
	if @rolename is not null
	begin
		-- VALIDATE GIVEN NAME
		if not exists (select * from sysusers where name = @rolename and issqlrole = 1)
		begin
			raiserror(15409, -1, -1, @rolename)
			return (1)
		end

		-- RESULT SET FOR SINGLE ROLE
		select DbRole = g.name, MemberName = u.name, MemberSID = u.sid
			from sysusers u, sysusers g, sysmembers m
			where g.name = @rolename
				and g.uid = m.groupuid
				and g.issqlrole = 1
				and u.uid = m.memberuid
			order by 1, 2
	end
	else
	begin
		-- RESULT SET FOR ALL ROLES
		select DbRole = g.name, MemberName = u.name, MemberSID = u.sid
			from sysusers u, sysusers g, sysmembers m
			where   g.uid = m.groupuid
				and g.issqlrole = 1
				and u.uid = m.memberuid
			order by 1, 2
	end

	return (0) -- sp_helprolemember
go


----------------------- sp_helprole -------------------------------------------

raiserror(15339,-1,-1,'sp_helprole')
go
CREATE PROCEDURE sp_helprole
	@rolename       sysname = NULL
AS
	if @rolename is not null
	begin
		-- VALIDATE GIVEN NAME
		if not exists (select * from sysusers where name = @rolename and (issqlrole = 1 or isapprole = 1))
		begin
			raiserror(15409, -1, -1, @rolename)
			return (1)
		end

		-- RESULT SET FOR SINGLE ROLE
		select 'RoleName' = name, 'RoleId' = uid, 'IsAppRole' = isapprole
			from sysusers where (name = @rolename) and (issqlrole = 1 or isapprole = 1)
	end
	else
	begin
		-- RESULT SET FOR ALL ROLES
		select 'RoleName' = name, 'RoleId' = uid, 'IsAppRole' = isapprole
			from sysusers where issqlrole = 1 or isapprole = 1
	end

	return (0) -- sp_helprole
go


----------------------- sp_helpntgroup ----------------------------------------

raiserror(15339,-1,-1,'sp_helpntgroup')
go
CREATE PROCEDURE sp_helpntgroup
	@ntname       sysname = NULL
AS
	if @ntname is not null
	begin
		-- VALIDATE GIVEN NAME
		if not exists (select * from sysusers where name = @ntname and isntgroup = 1)
		begin
			raiserror(15420, -1, -1, @ntname)
			return (1)
		end

		-- RESULT SET FOR SINGLE GROUP
		select 'NTGroupName' = name, 'NtGroupId' = uid, 'SID' = sid, 'HasDbAccess' = hasdbaccess
			from sysusers where name = @ntname and isntgroup = 1
	end
	else
	begin
		-- RESULT SET FOR ALL GROUPS
		select 'NTGroupName' = name, 'NtGroupId' = uid, 'SID' = sid, 'HasDbAccess' = hasdbaccess
			from sysusers where isntgroup = 1
	end

	return (0) -- sp_helpntgroup
go

------------------------------- xp_logininfo ----------------------------------

create proc xp_logininfo
	@acctname		sysname = null,				-- IN: NT login name
	@option			varchar(10) = null,			-- IN: 'all' | 'members' | null
	@privilege		varchar(10) = 'Not wanted' OUTPUT	-- OUT: 'admin' | 'user' | null
as
	-- VALIDATE PARAMETERS --
	if (@acctname is null AND (@option is not null OR (@privilege is null OR @privilege <> 'Not wanted')))
		OR ((@option is null OR @option <> 'all') AND (@privilege is null OR @privilege <> 'Not wanted'))
		OR (@option is not null and @option not in ('all', 'members'))
	begin
        raiserror(15600,-1,-1,'xp_logininfo')
        return 1
	end

	-- HANDLE CASE WHERE NO @acctname GIVEN --
	if (@acctname is null)
	begin
		select	'account name' = loginname,
				'type' = convert(varchar(8), case when isntuser = 1 then 'user' else 'group' end),
				'privilege' = convert(varchar(8), case when sysadmin = 1 then 'admin' else 'user' end),
				'mapped login name' = loginname,
				'permission path' = convert(sysname, null)
		from master..syslogins where isntname = 1 and hasaccess = 1
		order by 3, 1
		return @@error
	end

	-- HANDLE 'members' QUERY --
	if (@option = 'members')
	begin
		declare @priv varchar(8)
		select @priv = case when sysadmin = 1 then 'admin' else 'user' end
			from master..syslogins where isntname = 1 and loginname = @acctname and hasaccess = 1
		if @priv is not null
			select	'account name' = domain+N'\'+name,
					'type' = convert(varchar(8), case when sidtype = 1 then 'user' else 'group' end),
					'privilege' = @priv,
					'mapped login name' = domain+N'\'+name,
					'permission path' = @acctname
			from OpenRowset(NetGroupGetMembers, @acctname) order by 3, 1
		else
			select	'account name' = convert(sysname, null),
					'type' = convert(varchar(8), null),
					'privilege' = @priv,
					'mapped login name' = convert(sysname, null),
					'permission path' = convert(sysname, null)
			where 0=1	-- empty result set
		return @@error
	end

	-- CREATE TEMP TABLE AND POPULATE WITH THE REQUIRED DATA --
	create table #nt (name sysname collate database_default, sid varbinary(85), sidtype int)
	insert #nt select loginname, sid, isntgroup + 1 from master..syslogins
			where isntname = 1 and loginname = @acctname
	insert #nt select distinct domain+N'\'+name, sid, sidtype
			from OpenRowset(NetUserGetGroups, @acctname)
	if @@error <> 0
		return @@error
	-- IF ANY DENY, THEN NO ACCESS --
	if exists (select * from master..syslogins where sid in (select #nt.sid from #nt) and denylogin = 1)
		delete #nt

	-- HANDLE CASE WHERE OUTPUT REQUESTED --
	if (@privilege is null OR @privilege <> 'Not wanted')
	begin
		select @privilege = case max(sysadmin)
			when 1 then 'admin'
			when 0 then 'user'
			else NULL end
		from master..syslogins where isntname = 1 and hasaccess = 1
			AND sid in (select sid from #nt)
		return @@error
	end

	-- GET NT TYPE FOR NEXT OPTIONS --
	declare @type varchar(8)
	select @type = case when get_sid('\U'+@acctname, NULL) is null then 'group' else 'user' end

	-- HANDLE 'all' QUERY --
	if (@option = 'all')
	begin
		select	'account name' = @acctname,
				'type' = @type,
				'privilege' = convert(varchar(8), case when sysadmin = 1 then 'admin' else 'user' end),
				'mapped login name' = @acctname,
				'permission path' = case when l.loginname = @acctname then NULL else l.loginname end
		from master..syslogins l join #nt n on l.isntname = 1 and l.sid = n.sid
		where l.loginname = n.name and hasaccess = 1
		order by 3, 5
		return @@error
	end

	-- HANDLE DEFAULT QUERY --
	select	TOP 1
			'account name' = @acctname,
			'type' = @type,
			'privilege' = convert(varchar(8), case when sysadmin = 1 then 'admin' else 'user' end),
			'mapped login name' = @acctname,
			'permission path' = case when l.loginname = @acctname then NULL else l.loginname end
	from master..syslogins l join #nt n on l.isntname = 1 and l.sid = n.sid
	where l.loginname = n.name and hasaccess = 1
	order by 3, 5
	return @@error
go

-- GRANT PUBLIC ACCESS --
grant execute on sp_helpsrvrole            to public
grant execute on sp_srvrolepermission      to public
grant execute on sp_helpsrvrolemember      to public
grant execute on sp_helpdbfixedrole	       to public
grant execute on sp_dbfixedrolepermission  to public
grant execute on sp_helprolemember         to public
grant execute on sp_helprole               to public
grant execute on sp_helpntgroup            to public
-- NOTE: No grant for xp_logininfo! (it has no internal perm checks)
go
/************************  END-SECURITY-HELP-PROCEDURES  *********************/

/******************************************************************************
**************************  SYSSERVER-STORED-PROCS  ***************************
******************************************************************************/
checkpoint
go
if object_id('sp_addlinkedserver','P') IS NOT NULL
	drop procedure sp_addlinkedserver
if object_id('sp_dropserver','P') IS NOT NULL
	drop procedure sp_dropserver
if object_id('sp_serveroption','P') IS NOT NULL
	drop procedure sp_serveroption
if object_id('sp_addserver','P') IS NOT NULL
	drop procedure sp_addserver
if object_id('sp_setnetname','P') IS NOT NULL
	drop procedure sp_setnetname
if object_id('sp_helpserver','P') IS NOT NULL
	drop procedure sp_helpserver
if object_id('sp_helplinkedsrvlogin','P') IS NOT NULL
	drop procedure sp_helplinkedsrvlogin
go


----------------------------- sp_addlinkedserver ------------------------------

raiserror(15339,-1,-1,'sp_addlinkedserver')
go
create procedure sp_addlinkedserver
    @server         sysname,                -- server name
    @srvproduct     nvarchar(128) = NULL,   -- product name (dflt to ss)
    @provider       nvarchar(128) = NULL,   -- oledb provider name
    @datasrc        nvarchar(4000) = NULL,  -- oledb datasource property
    @location       nvarchar(4000) = NULL,  -- oledb location property
    @provstr        nvarchar(4000) = NULL,  -- oledb provider-string property
    @catalog        sysname = NULL          -- oledb catalog property
as
    -- VARIABLES
    declare @retcode    int,
            @srvid      smallint,
            @srvstat    smallint

    -- DISALLOW USER TRANSACTION
	set implicit_transactions off
    if @@trancount > 0
    begin
        raiserror(15002,-1,-1,'sp_addlinkedserver')
        return (1)
    end

    -- CHECK PERMISSIONS
    if not (is_srvrolemember('setupadmin') = 1)
    begin
		raiserror(15247,-1,-1)
        return (1)
    end

    -- VALIDATE SERVER NAME
    execute @retcode = sp_validname @server
    if @retcode <> 0
        return (1)

    -- SET DEFAULT STATUS BITS
    select @srvstat = 32 | 128  | 1024 -- local login mapping = 32, data access = 128 , use remote collation = 1024

    -- VALIDATE OLEDB PARAMETERS
    if @provider is null
    begin
        -- NO PROVIDER MEANS CANNOT SPECIFY ANY PROPERTIES!
        if @datasrc is not null or @location is not null or @provstr is not null or @catalog is not null
        begin
            raiserror(15426,-1,-1)
            return (1)
        end

        -- THIS MUST BE A WELL-KNOWN SERVER TYPE (DEFAULT IS SS)
        if @srvproduct is null OR lower(@srvproduct) = N'sql server'
        begin
            select @srvproduct = N'SQL Server'  -- force case to be this
            select @provider = N'SQLOLEDB'      -- SQL Server provider (LUXOR)
            select @datasrc = @server           -- datasrc is (network) server name
                        -- For SQL Server, we want rpc in/out by default
            select @srvstat = @srvstat | 1 | 64
                        -- rpc = 1, rpc out = 64
        end
        else            -- ADD OTHER WELL-KNOWN SOURCES HERE
        begin
            raiserror(15427,-1,-1,@srvproduct)
            return (1)
        end
    end
    else if @srvproduct in (N'SQL Server')  -- WELL-KNOWN SOURCES
    begin
        -- ILLEGAL TO SPECIFY PROVIDER/PROPERTIES FOR WELL-KNOWN SOURCES
        raiserror(15428,-1,-1,@srvproduct)
        return (1)
    end
    else if @srvproduct is null or lower(@srvproduct) like N'%sql server%'
    begin
        raiserror(15429,-1,-1,@srvproduct)
        return (1)
    end

    -- CHECK IF SERVER ALREADY EXISTS
    if exists (select * from master.dbo.sysservers where srvname = @server)
    begin
        raiserror(15028,-1,-1,@server)
        return (1)
    end

    -- GET SERVER ID FOR NEW ROW
    if not exists (select * from master.dbo.sysservers where srvid = 1)
        select @srvid = 1
    else
        select @srvid = min(s.srvid)+1 from master.dbo.sysservers s
            where s.srvid < 32767 and not exists
                (select * from master.dbo.sysservers s2 where s2.srvid = s.srvid+1)
    if @srvid is null
    begin
        raiserror(15430,-1,-1)
        return (1)
    end

    -- ADD ROW TO SYSSERVERS
	BEGIN TRAN
    insert master.dbo.sysservers select @srvid, @srvstat, @server, @srvproduct,
                @provider, @datasrc, @location, @provstr, getdate(), NULL, NULL, @catalog, NULL, 0, 0

	-- INSERT may have failed with row-too-big error.
	IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRAN
		RETURN (1)
	END

	-- ADD DEFAULT MAPPING FOR OUTGOING EVENTS
	insert into master.dbo.sysxlogins select
        @srvid, NULL, 192, getdate(), getdate(), NULL, NULL, 0, NULL
	COMMIT TRAN

    -- SUCCESS
    return (0) -- sp_addlinkedserver
go


------------------------------- sp_dropserver ---------------------------------

raiserror(15339,-1,-1,'sp_dropserver')
go
create procedure sp_dropserver
    @server     sysname,            -- server name
    @droplogins char(10) = NULL     -- drop all related logins?
as
	declare @ret int

    -- DISALLOW USER TRANSACTION
	set implicit_transactions off
    if @@trancount > 0
    begin
        raiserror(15002,-1,-1,'sp_dropserver')
        return (1)
    end

    -- CHECK PERMISSIONS
    if not (is_srvrolemember('setupadmin') = 1)
    begin
		raiserror(15247,-1,-1)
        return (1)
    end


    -- CHECK SERVER NAME / GET SERVER ID
    declare @srvid smallint
    select @srvid = srvid from master.dbo.sysservers where srvname = @server
    if @srvid is null
    begin
        raiserror(15015,-1,-1,@server)
        return (1)
    end

    -- CHECK @droplogins PARAMETER (FOR RELATED SYSREMOTELOGINS ROWS)
    if @droplogins is null
    begin
		-- DONT consider default mapping for outgoing events
        if exists (select * from master.dbo.sysxlogins
					where srvid = @srvid and
						  not(ishqoutmap = 1 and
							  xstatus&192 = 192 and
							  sid is null and
							  name is null and
							  password is null))
        begin
            raiserror(15190,-1,-1,@server)
            return (1)
        end
    end
    else if @droplogins <> 'droplogins'
    begin
        raiserror(15191,-1,-1)
        return (1)
    end

    -- CHECK TO SEE IF THE SERVER IS USED BY REPLICATION.
    if object_id('master.dbo.sp_MSrepl_check_server') is not null
    begin
        execute @ret = master.dbo.sp_MSrepl_check_server @server
        if @ret <> 0 or @@error <> 0
			return 1
    end

    -- DROP THE SERVER (ALONG WITH ANY REMOTE LOGINS)
	begin transaction
	delete master.dbo.sysxlogins where srvid = @srvid
	delete master.dbo.sysservers where srvid = @srvid
	commit transaction

	-- SUCCESS
	return (0) -- sp_dropserver
go

------------------------------ sp_serveroption --------------------------------

raiserror(15339,-1,-1,'sp_serveroption')
go
create procedure sp_serveroption
	@server		sysname,		-- server name to change
	@optname	varchar(35),	-- option name to turn on/off
	@optvalue	nvarchar(128)	-- true or false, on or off, collation name, or timeout value
as
    -- VARIABLES
	SET NOCOUNT ON
    declare @statvalue      smallint,   -- status bit of option
			@collationID	int,		-- on disk collation ID of the server
			@timeout		int,		-- value for setting timeout options
			@fSet			int,		-- 0 or 1 for setting boolean option
			@distributor	sysname		-- for checking for multiple dist servers

    -- DISALLOW USER TRANSACTION
	set implicit_transactions off
    if @@trancount > 0
    begin
        raiserror(15002,-1,-1,'sp_serveroption')
        return (1)
    end

    -- CHECK PERMISSIONS
    if not (is_srvrolemember('setupadmin') = 1)
    begin
		raiserror(15247,-1,-1)
        return (1)
    end

	-- RESOLVE SERVER NAME
	IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = @server)
    BEGIN
        raiserror(15015,-1,-1,@server)
        return (1)
    END

	-- HANDLE VARIOUS OPTIONS
    SELECT @optname = lower(@optname), @optvalue = lower(@optvalue)

	IF @optname = 'collation name'
	BEGIN
		-- Collation Name: May reset by string 'null' or NULL value
		IF @optvalue IS NULL OR @optvalue = 'null'
			OR COLLATIONPROPERTY(@optvalue, 'collationid') IS NOT NULL
		BEGIN
			SELECT @collationID = convert(int, COLLATIONPROPERTY(@optvalue, 'collationid'))
			if @optvalue is NOT NULL
				BEGIN
				if convert(int, COLLATIONPROPERTY(@optvalue, 'isunicodeonly')) = 1
					BEGIN
					raiserror(15301, -1, -1, @optvalue)
					return (1)
					END
				if convert(int, COLLATIONPROPERTY(@optvalue, 'issupportedbyos')) = 0
					BEGIN
					raiserror(15394, -1, -1, @optvalue)
					return (1)
					END
				END
			UPDATE master.dbo.sysservers SET srvcollation = @collationID,
				-- Turn 'collation compatible' off when srvcollation NOT NULL
				srvstatus = CASE WHEN @collationID IS NOT NULL THEN (srvstatus & ~256) ELSE srvstatus END,
				schemadate = getdate() WHERE srvname = @server
			RETURN 0
		END
	END
	ELSE IF @optname IN ('connect timeout','query timeout')
	BEGIN
		-- TIMEOUT OPTIONS: Value must be integer-numeric >= 0
		IF ISNUMERIC (@optvalue) = 1 AND convert(int, @optvalue) >= 0
		BEGIN
			SELECT @timeout = convert (int, @optvalue)
			IF @optname = 'connect timeout'
				UPDATE master.dbo.sysservers SET connecttimeout = @timeout,
					schemadate = getdate() WHERE srvname = @server
			ELSE
				UPDATE master.dbo.sysservers SET querytimeout = @timeout,
					schemadate = getdate() WHERE srvname = @server
			RETURN 0
		END
	END
	ELSE
	BEGIN
		-- BIT-VALUED OPTION: GET STATUS BIT AND WHETHER TO SET OR CLEAR --
		-- NOTE: CANNOT MAKE A SYSTEM SERVER INTO NON-SYSTEM --
		SELECT @statvalue = number FROM master.dbo.spt_values WHERE name = @optname and type = 'A'
		SELECT @fSet = CASE WHEN @optvalue IN ('true','on') THEN 1
				WHEN @optvalue IN ('false','off') AND @optname <> 'system' THEN 0
				ELSE NULL END
		IF @statvalue IS NOT NULL AND @fSet IS NOT NULL
		BEGIN
			-- ONLY ONE SERVER MAY BE A DISTRIBUTION SERVER
			IF @optname = 'dist' AND @optvalue in ('true', 'on')
			BEGIN
				SELECT @distributor = srvname from master.dbo.sysservers where (srvstatus & @statvalue) <> 0
				IF @distributor is not null
				BEGIN
					raiserror(14099,-1,-1, @distributor)
					RETURN 1
				END
			END
			ELSE IF @optname = 'lazy schema validation' AND @optvalue in ('true', 'on')
			BEGIN
				IF serverproperty('EngineEdition') <> 3 -- Enterprise edition
				BEGIN
					raiserror(17050,-1,-1, @optname)
					RETURN 1
				END
			END
			-- DO THE UPDATE
			UPDATE master.dbo.sysservers SET
				srvstatus = (srvstatus & ~@statvalue) | (@statvalue * @fSet),
				-- Set srvcollation NULL when turning 'collation compatible' on
				srvcollation = CASE WHEN @optname='collation compatible' AND @fSet=1 THEN NULL ELSE srvcollation END,
				schemadate = getdate() WHERE srvname = @server
			RETURN 0
		END
	END

	-- IF WE REACH HERE, WE HAVE AN INVALID PARAMETER
	raiserror(15600,-1,-1,'sp_serveroption')
	RETURN 1 -- sp_serveroption
go


-------------------------------- sp_addserver ---------------------------------

raiserror(15339,-1,-1,'sp_addserver')
go
create procedure sp_addserver
    @server         sysname,            --server name
    @local          varchar(10) = NULL, -- NULL or 'local'
    @duplicate_ok   varchar(13) = NULL  -- NULL or 'duplicate_ok'
as
    -- VARS
    declare @retcode		int

    -- CHECK IF SERVER ALREADY EXISTS
    if exists (select * from master.dbo.sysservers where srvname = @server)
    begin
        if @duplicate_ok = 'duplicate_ok'
            return (0)
        raiserror(15028,-1,-1,@server)
        return (1)
    end

    -- VALIDATE @local PARAMETER
    if @local is not null
    begin
        select @local = lower(@local)
        if @local <> 'local'
        begin
            raiserror(15379,-1,-1,@local)
            return (1)
        end

        -- ERROR IF ALREADY HAVE A LOCAL SERVER NAME
        if exists (select * from master.dbo.sysservers where srvid = 0)
        begin
            raiserror(15090,-1,-1)
            return (1)
        end
    end

    -- ADD THE SERVER (CHECKS PERMISSIONS, ETC)
    execute @retcode = sp_addlinkedserver @server
    if @retcode <> 0
        return @retcode

    -- SET THE SERVER ID IF LOCAL OPTION SPECIFIED
    if @local = 'local'
	begin
		declare @srvid smallint
		-- UPDATE DEFAULT MAPPING CREATED BY sp_addlinkedserver
		select @srvid = srvid from master.dbo.sysservers where srvname = @server
		update master.dbo.sysxlogins set srvid = 0 where srvid = @srvid
        update master.dbo.sysservers
			set srvid = 0,
				schemadate = getdate()
            where srvname = @server
	end

    -- FOR COMPATIBILITY, TURN OFF THE data access SERVER OPTION
    execute @retcode = sp_serveroption @server, 'data access', 'off'
    if @retcode <> 0
        return @retcode

	--SET 'local login mapping', 'off' (make rpc-s behave as in 6.5)
	update master.dbo.sysservers
	set srvstatus = srvstatus & ~32, schemadate = getdate()
            where srvname = @server

    -- SUCCESS
    return (0) -- sp_addserver
go


------------------------------- sp_setnetname ---------------------------------

raiserror(15339,-1,-1,'sp_setnetname')
go
create procedure sp_setnetname  --- 1995/12/22 13:07
	 @server	sysname	-- server name
	,@netname	sysname	-- new net name
as
    DECLARE @srvproduct     nvarchar(128)   -- product name; must be SQL Server
	DECLARE @srvstatus int

    -- DISALLOW USER TRANSACTION
	set implicit_transactions off
    if @@trancount > 0
    begin
        raiserror(15002,-1,-1,'sp_setnetname')
        return (1)
    end

    -- CHECK PERMISSIONS
    if not (is_srvrolemember('setupadmin') = 1)
    begin
		raiserror(15247,-1,-1)
        return (1)
    end

    -- CHECK SERVER NAME (MUST BE A SQL SERVER!)
	select @srvproduct = srvproduct, @srvstatus = srvstatus from master.dbo.sysservers
                   where srvname = @server

	if @srvproduct is NULL
    begin
	    raiserror(15015,-1,-1,@server)
        return (1)
    end
	-- case checking is performed at sp_addlinkedserver so direct comparison is OK here
    else if @srvproduct <> N'SQL Server'
    begin
		raiserror(15576,-1,-1,@server)
		return (1)
    end

	-- CHECK FOR LOOPBACK SERVER AND ISSUE WARNING
	-- Only check linked server for "data access" since that's where the limitation
	-- lies.  Replication calls this code although only for RPC servers, so they
	-- shouldn't be seeing this message
	if @netname = @@SERVERNAME and @srvstatus & 128 = 128 and @server <> @netname
	begin
		raiserror(15577,-1,-1)
	end

    -- DO THE UPDATE
    update master.dbo.sysservers set datasource = @netname, schemadate = getdate()
        where srvname = @server

    -- SUCCESS
    return (0) -- sp_setnetname
go

------------------------------- sp_helpserver ---------------------------------

raiserror(15339,-1,-1,'sp_helpserver')
go
create procedure sp_helpserver
    @server         sysname = NULL,         -- server name
    @optname        varchar(35) = NULL,     -- option name to limit results
    @show_topology  varchar(1) = NULL       -- 't' to show topology coordinates
as
    -- PRELIMINARY
    set nocount on
    declare @optbit     int,
            @bitdesc    sysname,
            @curbit     int

    -- CHECK IF REQUESTED SERVER(S) EXIST
    if not exists (select * from master.dbo.sysservers where
		(@server is null or srvname = @server))
    begin
        if @server is null
            raiserror(15205,-1,-1)
        else
            raiserror(15015,-1,-1,@server)
        return (1)
    end

    -- GET THE BIT VALUE(S) FOR THE OPTION REQUESTED
    if @optname is not null
    begin
        select @optbit = number from master.dbo.spt_values
            where type = 'A' and name = @optname
        if @optbit is null
        begin
            raiserror(15206,-1,-1,@optname)
            return(1)
        end
    end
    else
        select @optbit = -1     -- 0xffffffff

    -- MAKE WORK COPY OF RELEVANT PART OF SYSSERVERS
    select name = srvname, network = srvnetname, status = convert(varchar(100), ''),
            id = srvid, srvstat = srvstatus, topx = topologyx, topy = topologyy,
			collation_name = convert(sysname, CollationPropertyFromID(srvcollation, 'name')),
			connect_timeout = connecttimeout, query_timeout = querytimeout
        into #spt_server
        from master.dbo.sysservers
		where (@server is null or srvname = @server) and (@optname is null or srvstatus & @optbit <> 0)

    -- SET THE STATUS FIELD
    select @curbit = 1
    while @curbit < 0x10000 -- bit field is a smallint
    begin
        select @bitdesc = null
        select @bitdesc = name from master.dbo.spt_values
			where type = 'A' and number = @curbit
        if @bitdesc is not null
            update #spt_server set status = status + ',' + @bitdesc where srvstat & @curbit <> 0
        select @curbit = @curbit * 2
    end

    -- SHOW THE RESULT SET
    if lower(@show_topology) <> 't' or @show_topology is null
	    select name, network_name = substring(network, 1, 28),
		        status = isnull(substring(status,2,8000),''),
                id = convert(char(4), id),
				collation_name, connect_timeout, query_timeout
	    from #spt_server order by name
    else
	    select name, network_name = substring(network, 1, 28),
		        status = isnull(substring(status,2,8000),''),
                id = convert(char(4), id),
				collation_name, connect_timeout, query_timeout,
				topx, topy
	    from #spt_server order by name

    -- RETURN SUCCESS
    return(0) -- sp_helpserver
go

------------------------------- sp_helpserver ---------------------------------

raiserror(15339,-1,-1,'sp_helplinkedsrvlogin')
go
create procedure sp_helplinkedsrvlogin
	@rmtsrvname		sysname = NULL,
	@locallogin		sysname = NULL
as
	declare	@srvid	smallint,
			@status	smallint,
			@ret	int

    -- CHECK REMOTE SERVER NAME.
    if @rmtsrvname is not null
    begin
    	select @srvid = srvid from master.dbo.sysservers where srvname = @rmtsrvname
    	if @srvid is null
    	begin
		raiserror(15015,-1,-1,@rmtsrvname)
		return (1)
    	end
    end

    -- IF SPECIFIED CHECK LOCAL USER NAME
	if (@locallogin IS NOT NULL)
	begin
		select	u.srvname as [Linked Server],		t.name as [Local Login],
				s.selfoutmap as [Is Self Mapping],	s.name as [Remote Login]
		from master.dbo.sysxlogins s, master.dbo.sysxlogins t, master.dbo.sysservers u
		where ((@rmtsrvname is null or @rmtsrvname=u.srvname) and u.srvid= s.srvid)
			and s.ishqoutmap = 1 and s.sid=t.sid
			and t.name = @locallogin and t.ishqoutmap = 0
	end

	if (@locallogin IS  NULL)
	begin
		-- Get global mapping (s.sid is NULL) if any
		select	u.srvname as [Linked server],		NULL as [Local Login],
			s.selfoutmap as [Is Self Mapping],	s.name as [Remote Login]
		from master.dbo.sysxlogins s, master.dbo.sysservers u
		where  ((@rmtsrvname is null or @rmtsrvname=u.srvname)and u.srvid= s.srvid)
			and s.ishqoutmap = 1 and  s.sid is NULL
		UNION
		-- Get specific mappings
		select	u.srvname as [Linked server],		t.name as [Local Login],
				s.selfoutmap as [Is Self Mapping],	s.name as [Remote Login]
		from master.dbo.sysxlogins s, master.dbo.sysxlogins t, master.dbo.sysservers u
		where ((@rmtsrvname is null or @rmtsrvname=u.srvname) and u.srvid= s.srvid)
			and s.ishqoutmap = 1 and s.sid=t.sid and t.ishqoutmap = 0
		order by u.srvname
    end
    -- RETURN SUCCESS
    return(0) -- sp_helplinkedsrvlogin
go

-- GRANT PUBLIC ACCESS --
grant execute on sp_addlinkedserver to public
grant execute on sp_dropserver to public
grant execute on sp_serveroption to public
grant execute on sp_addserver to public
grant execute on sp_setnetname to public
grant execute on sp_helpserver to public
grant execute on sp_helplinkedsrvlogin to public
go
/************************  END-SYSSERVER-STORED-PROCS  ***********************/

/************************  SYSOLEDBUSERS-STORED-PROCS  ***********************/
if object_id('sp_addlinkedsrvlogin','P') IS NOT NULL
	drop procedure sp_addlinkedsrvlogin
if object_id('sp_droplinkedsrvlogin','P') IS NOT NULL
	drop procedure sp_droplinkedsrvlogin
go

------------------------------- sp_addlinkedsrvlogin ---------------------------------

raiserror(15339,-1,-1,'sp_addlinkedsrvlogin')
go
create procedure sp_addlinkedsrvlogin
	@rmtsrvname		sysname,
	@useself		varchar(8) = 'true',
	@locallogin		sysname = NULL,
	@rmtuser    	sysname = NULL,
	@rmtpassword	sysname = NULL
as
	declare	@srvid	smallint,
			@status	smallint,
			@localsid	varbinary(85),
			@ret	int

    -- DISALLOW USER TRANSACTION
	set implicit_transactions off
    if @@trancount > 0
    begin
        raiserror(15002,-1,-1,'sp_addlinkedsrvlogin')
        return (1)
    end

    -- CHECK PERMISSIONS
    if not (is_srvrolemember('securityadmin') = 1)
    begin
		raiserror(15247,-1,-1)
        return (1)
    end

	-- VALIDATE @useself PARAMETER --
	select @useself = lower(@useself)
	if @useself is null or @useself not in ('true','false')
	begin
        raiserror(15600,-1,-1,'sp_addlinkedsrvlogin')
        return 1
	end

	-- CHECK REMOTE SERVER NAME.
	select @srvid = srvid from master.dbo.sysservers where srvname = @rmtsrvname
	if @srvid is null
	begin
		raiserror(15015,-1,-1,@rmtsrvname)
		return (1)
	end

	-- IF SPECIFIED CHECK LOCAL USER NAME
	if (@locallogin IS NOT NULL)
	begin
		select @localsid = sid from master.dbo.syslogins where loginname = @locallogin
		if @localsid IS NULL
		begin
			-- ADD ROW FOR NT LOGIN IF NEEDED --
			execute @ret = sp_MSaddlogin_implicit_ntlogin @locallogin
			if (@ret = 0)
				select @localsid = sid from master.dbo.syslogins where loginname = @locallogin
			if (@localsid IS NULL)
			begin
				raiserror(15067,-1,-1,@locallogin)
				return (1)
			end
		end
	end

	-- 64 IMPLIES sysxlogins::ishqoutmap is TRUE
	select @status = 64

	-- IF @useself IS TRUE IT OVERRIDES PARAMETERS @rmtuser, and @rmtpassword
	if @useself = 'true'
	begin
		select @rmtuser = NULL
		select @rmtpassword = NULL
		select @status = @status | 128
	end

	BEGIN TRAN

	-- DELETE EXISTING MAPPING(s) FOR THIS @sid
	update master.dbo.sysxlogins set xstatus = xstatus & ~192
		where srvid = @srvid AND ishqoutmap = 1 AND isrpcinmap = 1
			AND ((sid IS NULL AND @localsid IS NULL) OR sid = @localsid)
	if @@rowcount = 0
		delete master.dbo.sysxlogins where srvid = @srvid AND ishqoutmap = 1
			AND ((sid IS NULL AND @localsid IS NULL) OR sid = @localsid)

	-- ATTEMPT TO TAG THIS ONTO EXISTING ROW --
	update master.dbo.sysxlogins
		set xstatus = (xstatus & ~192) | @status,
			xdate2 = getdate(),
			password = convert(varbinary(256), encrypt(@rmtpassword))
		where srvid = @srvid AND isrpcinmap = 1
			AND ((sid IS NULL AND @localsid IS NULL) OR sid = @localsid)
			AND ((name IS NULL AND @rmtuser IS NULL) OR name = @rmtuser)

	-- IF NO ROW UPDATED, INSERT NEW ROW --
	if (@@rowcount = 0)
		insert master.dbo.sysxlogins values
				(@srvid, @localsid, @status, getdate(), getdate(), @rmtuser,
					   convert(varbinary(256), encrypt(@rmtpassword)), 0, NULL)

	COMMIT TRAN

    -- RETURN SUCCESS
    return(0) -- sp_addlinkedsrvlogin
go

------------------------------- sp_droplinkedsrvlogin ---------------------------------

raiserror(15339,-1,-1,'sp_droplinkedsrvlogin')
go
create procedure sp_droplinkedsrvlogin
	@rmtsrvname		sysname,
	@locallogin		sysname
as
	declare @srvid	smallint
	declare @localsid	varbinary(85)

    -- DISALLOW USER TRANSACTION
	set implicit_transactions off
    if @@trancount > 0
    begin
        raiserror(15002,-1,-1,'sp_droplinkedsrvlogin')
        return (1)
    end

    -- CHECK PERMISSIONS
    if not (is_srvrolemember('securityadmin') = 1)
    begin
		raiserror(15247,-1,-1)
        return (1)
    end

	-- CHECK REMOTE SERVER NAME.
	select @srvid = srvid from master.dbo.sysservers where srvname = @rmtsrvname
	if @srvid is null
	begin
		raiserror(15015,-1,-1,@rmtsrvname)
		return (1)
	end

	-- CHECK LOCAL USER NAME IF GIVEN
	if @locallogin is not null
	begin
		select @localsid = suser_sid(@locallogin)
		if (@localsid IS NULL)
		begin
			raiserror(15067,-1,-1,@locallogin)
			return (1)
		end
	end

	-- DELETE MAPPING(s)
	update master.dbo.sysxlogins set xstatus = xstatus & ~192
		where srvid = @srvid AND ishqoutmap = 1 AND isrpcinmap = 1
			AND ((sid IS NULL AND @localsid IS NULL) OR sid = @localsid)
	if @@rowcount = 0
		delete master.dbo.sysxlogins where srvid = @srvid AND ishqoutmap = 1
			AND ((sid IS NULL AND @localsid IS NULL) OR sid = @localsid)

    -- RETURN SUCCESS
    return(0) -- sp_droplinkedsrvlogin
go

-- GRANT PUBLIC ACCESS --
grant execute on sp_addlinkedsrvlogin to public
grant execute on sp_droplinkedsrvlogin to public
go
/************************  END-SYSOLEDBUSERS-STORED-PROCS  ***********************/

/******************************************************************************
****************************  FULLTEXT INDEXES ********************************
******************************************************************************/
checkpoint
go
if object_id('sp_fulltext_service','P') IS NOT NULL
	drop procedure sp_fulltext_service
if object_id('sp_fulltext_database','P') IS NOT NULL
	drop procedure sp_fulltext_database
if object_id('sp_fulltext_catalog','P') IS NOT NULL
	drop procedure sp_fulltext_catalog
if object_id('sp_fulltext_table','P') IS NOT NULL
	drop procedure sp_fulltext_table
if object_id('sp_fulltext_column','P') IS NOT NULL
	drop procedure sp_fulltext_column
	if object_id('sp_help_fulltext_catalogs','P') IS NOT NULL
	drop procedure sp_help_fulltext_catalogs
if object_id('sp_help_fulltext_catalogs_cursor','P') IS NOT NULL
	drop procedure sp_help_fulltext_catalogs_cursor
if object_id('sp_help_fulltext_tables','P') IS NOT NULL
	drop procedure sp_help_fulltext_tables
if object_id('sp_help_fulltext_tables_cursor','P') IS NOT NULL
	drop procedure sp_help_fulltext_tables_cursor
if object_id('sp_help_fulltext_columns','P') IS NOT NULL
	drop procedure sp_help_fulltext_columns
if object_id('sp_help_fulltext_columns_cursor','P') IS NOT NULL
	drop procedure sp_help_fulltext_columns_cursor
if object_id('sp_fulltext_getdata','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_fulltext_getdata'
execute sp_addextendedproc 'sp_fulltext_getdata','(server internal)'
go


---------------------------- sp_fulltext_service ------------------------------

raiserror(15339,-1,-1,'sp_fulltext_service')
go
create proc sp_fulltext_service
    @action     varchar(20),    -- resource_usage | clean_up | connect_timeout | data_timeout
    @value      int = NULL      -- value for resource_usage | connect_timeout | data_timeout
as
	-- VALIDATE PARAMS --
	if @action is null OR @action not in ('resource_usage', 'clean_up', 'connect_timeout', 'data_timeout')
        OR (@value is not null AND @action not in ('resource_usage', 'connect_timeout', 'data_timeout'))
        OR (@value is null and @action in ('resource_usage', 'connect_timeout', 'data_timeout'))
	begin
        raiserror(15600,-1,-1,'sp_fulltext_service')
        return 1
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
    if @@trancount > 0
    begin
        raiserror(15002,-1,-1,'sp_fulltext_service')
        return 1
    end

	-- CHECK PERMISSIONS (must be serveradmin) --
	if (is_srvrolemember('serveradmin') = 0)
    begin
        raiserror(15247,-1,-1)
        return 1
    end

    if @action = 'resource_usage'
    begin
        DBCC CALLFULLTEXT ( 13, @value )  -- FTSetResource( @value )
        if @@error <> 0
            return 1
    end

	if @action = 'clean_up'
	begin
		DBCC CALLFULLTEXT ( 8 )	-- Iterate thru catalogs, remove if dbid doesn't exist.
		if @@error <> 0
			return 1
	end

    if @action = 'connect_timeout'
    begin
        DBCC CALLFULLTEXT ( 14, @value )    -- SetProperty( FT_PROP_CONN_TIMEOUT,  @value )
        if @@error <> 0
            return 1
    end

	if @action = 'data_timeout'
    begin
        DBCC CALLFULLTEXT ( 15, @value )    -- SetProperty( FT_PROP_DATA_TIMEOUT,  @value )
        if @@error <> 0
            return 1
    end


	-- SUCCESS --
	return 0	-- sp_fulltext_service
go

---------------------------- sp_fulltext_database -----------------------------

raiserror(15339,-1,-1,'sp_fulltext_database')
go
create proc sp_fulltext_database
	@action		varchar(20)		-- 'enable' | 'disable'
as
	declare @ftcat		sysname,
		    @ftcatid	smallint,
			@path		nvarchar(260),
			@objid		int,
			@dbid		smallint,
			@objname	sysname

	-- VALIDATE PARAMS --
	if @action is null OR @action not in ('enable','disable')
	begin
        raiserror(15600,-1,-1,'sp_fulltext_database')
        return 1
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
    if @@trancount > 0
    begin
        raiserror(15002,-1,-1,'sp_fulltext_database')
        return 1
    end

	-- CHECK PERMISSIONS (must be a dbowner) --
	if (is_member('db_owner') = 0)
    begin
        raiserror(15247,-1,-1)
        return 1
    end

	-- CHECK DATABASE MODE (must not be read-only) --
	if (DATABASEPROPERTY(db_name(), 'IsReadOnly') = 1)
	begin
		raiserror(15635, -1, -1, 'sp_fulltext_database')
		return 1
	end

	-- CLEAR SYSDATABASES BIT AND PROPAGATE W/ CHECKPOINT (for both enable & disable) --
	select @dbid = db_id()
	update master.dbo.sysdatabases set status2 = status2 & ~536870912 where dbid = @dbid
	checkpoint

	-- DROP ALL CATALOGS WITH THIS DATABASE (for both enable/disable) --
	DBCC CALLFULLTEXT ( 7, @dbid )	-- FTDropAllCatalogs ( "@dbid" )
	if @@error <> 0
		return 1

	-- DELETE ALL THE CHANGE NOTIFICATIONS FROM SYSFULLTEXTNOTIFY --
	delete sysfulltextnotify

	if @action = 'enable'
	begin
		-- CREATE CATALOGS --
		declare ms_crs_ftcat cursor static local for select name, path from sysfulltextcatalogs
		open ms_crs_ftcat
		fetch ms_crs_ftcat into @ftcat, @path
		while @@fetch_status >= 0
		begin
			DBCC CALLFULLTEXT ( 16, @ftcat, @path )	-- FTCreateCatalog( @ftcatid, @path )
			if @@error <> 0
				return 1
			fetch ms_crs_ftcat into @ftcat, @path
		end
		deallocate ms_crs_ftcat

		declare	@vc1			nvarchar(517)
		-- BEGIN TRAN
		begin tran

		-- ACTIVATE TABLES/URLs --
		declare ms_crs_ftind cursor static local for select ftcatid, id from sysobjects
					where (ftcatid <> 0)

		open ms_crs_ftind
		fetch ms_crs_ftind into @ftcatid, @objid
		while @@fetch_status >= 0
		begin
			DBCC CALLFULLTEXT ( 5, @ftcatid, @objid )	-- FTAddURL( @ftcatid, db_id(), @objid )
			if @@error <> 0
				goto error_abort_exit

			-- CHECK TABLE FOR NOTIFICATIONS --
			if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1
			begin
				-- ERROR IF DATABASE IS IN SINGLE USER MODE --
				if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
				begin
					select @objname = object_name(@objid)
					raiserror(15638, -1, -1, @objname)

					select @vc1 = quotename(user_name(OBJECTPROPERTY(@objid,'OwnerId'))) + '.'
						+ quotename(@objname)

					-- LOCK TABLE --
					dbcc lockobjectschema(@vc1)
					if @@error <> 0
						goto error_abort_exit

					-- TURN OFF CHANGE TRACKING ACTIVE BITS IN SYSOBJECTS --
					update sysobjects set status = status & ~192 where id = @objid

					fetch ms_crs_ftind into @ftcatid, @objid
					continue
				end

				-- START A FULL CRAWL FOR THIS TABLE --
				DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
				if @@error <> 0
					goto error_abort_exit
			end

			-- CHECK TABLE FOR AUTOPROPAGATION  -
			if ObjectProperty(@objid, 'TableFulltextBackgroundUpdateIndexOn') = 1
			begin
				DBCC CALLFULLTEXT ( 10, @ftcatid, @objid )	-- FTEnableAutoProp( @ftcatid, db_id(), @objid )
				if @@error <> 0
					goto error_abort_exit
			end

			fetch ms_crs_ftind into @ftcatid, @objid
		end
		deallocate ms_crs_ftind

		-- SET SYSDATABASES BIT --
		update master.dbo.sysdatabases set status2 = status2 | 536870912 where dbid = @dbid

		-- COMMIT TRAN --
		commit tran

		-- CHECKPOINT TO PUSH SYSDATABASES BIT TO MEMORY --
		checkpoint

		if @@error <> 0
			goto error_abort_exit

	end


	-- SUCCESS --
	return 0	-- sp_fulltext_database


error_abort_exit:
	rollback tran
	return 1	-- sp_fulltext_database
go

---------------------------- sp_fulltext_catalog ------------------------------

raiserror(15339,-1,-1,'sp_fulltext_catalog')
go
create proc sp_fulltext_catalog
	@ftcat		sysname,		-- full-text catalog name
	@action 	varchar(20),	-- create | drop | | rebuild | ...
	@path		nvarchar(100) = null	-- optional file path for create (max of 100 chars!!!)
as
	declare @objname sysname,
			@objid	int,
			@vc1	nvarchar(517),
			@tabname	nvarchar(517),
			@tabwarn int

	select @tabwarn = 0

	-- FULLTEXT MUST BE ACTIVE IN DATABASE --
	if DatabaseProperty(db_name(), 'IsFulltextEnabled') = 0
	begin
		raiserror(15601,-1,-1)
		return 1
	end

	-- VALIDATE PARAMS --
	if @action is null
		OR @action not in ('create','drop','start_full','start_incremental','stop','rebuild')
		OR @ftcat is null OR len(@ftcat) = 0
		OR (@path is not null and @action <> 'create')
	begin
		raiserror(15600,-1,-1,'sp_fulltext_catalog')
		return 1
	end

	-- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	if @@trancount > 0
	begin
		raiserror(15002,-1,-1,'sp_fulltext_catalog')
		return 1
	end

	-- CHECK PERMISSIONS (must be a dbowner) --
	if (is_member('db_owner') = 0)
	begin
		raiserror(15247,-1,-1)
		return 1
	end

	-- CHECK DATABASE MODE (must not be read-only) --
	if DATABASEPROPERTY(db_name(), 'IsReadOnly') = 1
	begin
		raiserror(15635, -1, -1, 'sp_fulltext_catalog')
		return 1
	end

	-- CATALOG MUST EXIST IF NOT CREATING --
	declare @ftcatid smallint
	select @ftcatid = ftcatid from sysfulltextcatalogs where name = @ftcat
	if @action not in ('create', 'drop') and @ftcatid is null
	begin
		raiserror(7641,-1,-1,@ftcat)
		return 1
	end

	if @action = 'create'
	begin
		DBCC CALLFULLTEXT ( 1, @ftcat, @path )	-- FTCreateCatalog( @ftcat, @path )
		if @@error <> 0 
			return 1
	end

	if @action = 'drop'
	begin
		-- CANNOT DROP CATALOG IF USED --
		if exists (select * from sysobjects where ftcatid = @ftcatid)
		begin
			raiserror(15604,-1,-1, @ftcat)
			return 1
		end

		DBCC CALLFULLTEXT ( 2, @ftcat )	-- FTDropCatalog( @ftcat )
		if @@error <> 0
			return 1
	end

	if @action = 'start_full'
	begin
		-- ERROR IF DATABASE IS IN SINGLE USER MODE --
		if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
		begin
			raiserror(15636, -1, -1, @ftcat)
			return 1
		end

		begin tran
		-- MARK TABLES/URLs AS --
		declare ms_crs_ftind cursor static local for select id, name from sysobjects
					where ftcatid = @ftcatid
		open ms_crs_ftind
		fetch ms_crs_ftind into @objid, @tabname
		while @@fetch_status >= 0
		begin

			-- ERROR ON TABLE IF TABLE IS NOT ACTIVATED --
			if (ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0)
			begin
				raiserror(15630, -1, -1, @tabname)
				goto error_exit
			end

			-- SKIP TABLE IF CRAWL ALREADY IN PROGRESS --
			if (ObjectProperty(@objid, 'TableFulltextPopulateStatus') != 0)
			begin
				select @tabwarn = 1
				fetch ms_crs_ftind into @objid, @tabname
				continue
			end


			-- START FULL CRAWL
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
			if @@error <> 0	-- server raised an error
			begin
				-- server did an ex_raise - this is unreachable code
				goto error_exit
			end

			-- DELETE NOTIFICATIONS FROM SYSFULLTEXTNOTIFY --
			delete sysfulltextnotify where tableid = @objid

			if (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 1)
			and (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0)
			begin

				select @vc1 = quotename(user_name(OBJECTPROPERTY(@objid,'OwnerId'))) + '.'
							+ quotename(object_name(@objid))

				dbcc lockobjectschema(@vc1)

				update sysobjects set status = status & ~128 where id = @objid

			end

			fetch ms_crs_ftind into @objid, @tabname
		end
		deallocate ms_crs_ftind

		commit tran

	end

	if @action = 'start_incremental'
	begin
		-- ERROR IF DATABASE IS IN SINGLE USER MODE --
		if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
		begin
			raiserror(15636, -1, -1, @ftcat)
			return 1
		end
		begin tran

		-- MARK TABLES/URLs AS --
		declare ms_crs_ftind cursor static local for select id, name from sysobjects
					where ftcatid = @ftcatid
		open ms_crs_ftind
		fetch ms_crs_ftind into @objid, @tabname
		while @@fetch_status >= 0
		begin
			-- ERROR ON TABLE IF TABLE IS NOT ACTIVATED --
			if (ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0)
			begin
				raiserror(15630, -1, -1, @tabname)
				goto error_exit
			end

			-- SKIP TABLE IF CRAWL ALREADY IN PROGRESS --
			if (ObjectProperty(@objid, 'TableFulltextPopulateStatus') != 0)
			begin
				select @tabwarn = 1
				fetch ms_crs_ftind into @objid, @tabname
				continue
			end

			if (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 1)
			and (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0)
			begin

				-- START A FULL POPULATION FOR THIS TABLE --
				DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
				if @@error <> 0
				begin
					-- server did an ex_raise - this is unreachable code --
					goto error_exit
				end

				select @vc1 = quotename(user_name(OBJECTPROPERTY(@objid,'OwnerId'))) + '.'
						+ quotename(object_name(@objid))

				dbcc lockobjectschema(@vc1)

				update sysobjects set status = status & ~128 where id = @objid

			end
			else
			begin
				-- START AN INCREMENTAL POPULATION FOR THIS TABLE --
				DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 1 )
				if @@error <> 0
				begin
					-- server did an ex_raise - this is unreachable code --
					goto error_exit
				end

			end

			-- DELETE NOTIFICATIONS FROM SYSFULLTEXTNOTIFY --
			delete sysfulltextnotify where tableid = @objid

			fetch ms_crs_ftind into @objid, @tabname
		end
		deallocate ms_crs_ftind
		commit tran


	end

	if @action = 'stop'
	begin
		declare ms_crs_ftind cursor static local for select id, name from sysobjects
					where ftcatid = @ftcatid
		open ms_crs_ftind
		fetch ms_crs_ftind into @objid, @tabname
		while @@fetch_status >= 0
		begin

			-- ERROR ON TABLE IF TABLE IS NOT ACTIVATED --
			if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0
			begin
				raiserror(15630, -1, -1, @tabname)
				return 1
			end

			-- SKIP TABLE IF CRAWL ALREADY STOPPED - NO WARNING --
			if (ObjectProperty(@objid, 'TableFulltextPopulateStatus') = 0)
			begin
				fetch ms_crs_ftind into @objid, @tabname
				continue
			end

			-- ERROR IF POPULATE STATUS OF THE TABLE IS CRAWLING AND CT ON
			if (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1)
			and ((ObjectProperty(@objid, 'TableFulltextPopulateStatus') = 1)
			or (ObjectProperty(@objid, 'TableFulltextPopulateStatus') = 2))
			begin
				raiserror(15642,-1,-1, @tabname)
				return 1
			end

			-- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
			if @@error <> 0
			begin
				-- server did an ex_raise - this is unreachable code --
				return 1
			end

			fetch ms_crs_ftind into @objid, @tabname
		end
		deallocate ms_crs_ftind

	end

	if @action = 'rebuild'
	begin

		-- RE-CREATE CATALOG (Will first drop)
		select @path = path from sysfulltextcatalogs where ftcatid = @ftcatid
		DBCC CALLFULLTEXT ( 16, @ftcat, @path )	-- FTCreateCatalog( @ftcat, @path )
		if @@error <> 0
		begin 
			-- server did an ex_raise - this is unreachable code --
			return 1
		end

		begin tran

		-- RE-ACTIVATE TABLES/URLs --
		declare ms_crs_ftind cursor static local for select id from sysobjects
					where ftcatid = @ftcatid
		open ms_crs_ftind
		fetch ms_crs_ftind into @objid
		while @@fetch_status >= 0
		begin
			DBCC CALLFULLTEXT ( 5, @ftcatid, @objid )	-- FTAddURL( @ftcat, db_id(), @objid )
			if @@error <> 0
			begin
				-- server did an ex_raise - this is unreachable code --
				goto error_exit
			end

			-- CHECK TABLE FOR NOTIFICATIONS --
			if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1
				and ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 1
			begin
				-- ERROR IF DATABASE IS IN SINGLE USER MODE --
				if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
				begin
					select @objname = object_name(@objid)
					raiserror(15638, -1, -1, @objname)

					select @vc1 = quotename(user_name(OBJECTPROPERTY(@objid,'OwnerId'))) + '.'
						+ quotename(@objname)


					dbcc lockobjectschema(@vc1)

					-- DISABLE FULLTEXT AUTO PROPAGATION (NO ERROR IF ALREADY DISABLED AND --
					-- IGNORE ANY OTHER ERRORS) --
					DBCC CALLFULLTEXT ( 9, @objid )	-- FTDisableNotify( db_id(), @objid )
					if @@error <> 0
					begin
						-- server did an ex_raise - this is unreachable code --
						goto error_exit
					end

					-- TURN OFF CHANGE TRACKING ACTIVE BITS IN SYSOBJECTS --
					update sysobjects set status = status & ~192 where id = @objid

					fetch ms_crs_ftind into @objid
					continue
				end

				-- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
				DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
				if @@error <> 0
				begin
					-- server did an ex_raise - this is unreachable code --
					goto error_exit
				end

				-- START A FULL CRAWL FOR THIS TABLE --
				DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
				if @@error <> 0
				begin
					-- server did an ex_raise - this is unreachable code --
					goto error_exit
				end

				-- DELETE NOTIFICATIONS FROM SYSFULLTEXTNOTIFY --
				delete sysfulltextnotify where tableid = @objid

			end

			-- CHECK TABLE FOR AUTOPROPAGATION  -
			if ObjectProperty(@objid, 'TableFulltextAutoPropagationOn') = 1
				and ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 1
			begin
				DBCC CALLFULLTEXT ( 10, @ftcatid, @objid )	-- FTEnableAutoProp( @ftcatid, db_id(), @objid )
				if @@error <> 0
				begin
					-- server did an ex_raise - this is unreachable code --
					goto error_exit
				end
			end

			fetch ms_crs_ftind into @objid
		end
		deallocate ms_crs_ftind

		commit tran

	end
	if(@tabwarn <> 0)
	begin
		raiserror(15643, -1, -1)
		return 0
	end
	return 0	-- sp_fulltext_catalog

error_exit:
	-- 'stop', 'rebuild' never get here, this is only for 'start_full', 'start_incr'
	-- here we commit the changes for all tables on which the operation succeeded.  
	-- Before 'goto error_exit' is called, schema changes made to table currently 
	-- under cursor must be undone (so far, no schema changes)
	commit tran
	return 1	-- sp_fulltext_catalog

go

----------------------------- sp_fulltext_table -------------------------------

raiserror(15339,-1,-1,'sp_fulltext_table')
go
create proc sp_fulltext_table
	@tabname	nvarchar(517),
	@action		varchar(50),
	@ftcat		sysname = NULL,		-- create: catalog name
	@keyname	sysname = NULL		-- create: name of unique index
as
	declare @schemamodified int
	-- FULLTEXT MUST BE ACTIVE IN DATABASE --
	if DatabaseProperty(db_name(), 'IsFulltextEnabled') = 0
	begin
        raiserror(15601,-1,-1)
        return 1
	end

	-- VALIDATE PARAMS --
	if @action is null
		OR @action not in ('create','drop','activate','deactivate',
			'start_change_tracking', 'stop_change_tracking',
			'start_background_updateindex', 'stop_background_updateindex',
			'update_index', 'start_full', 'start_incremental', 'stop')
		OR (@action not in ('create') and (@ftcat is not null or @keyname is not null))
		OR (@action in ('create') and (@ftcat is null or @keyname is null))
	begin
        raiserror(15600,-1,-1,'sp_fulltext_table')
        return 1
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
    if @@trancount > 0
    begin
        raiserror(15002,-1,-1,'sp_fulltext_table')
        return 1
    end

	-- VALIDATE TABLE NAME --
    --  (1) Must exist in current database
	declare @objid int
	select @objid = object_id(@tabname, 'local')
    if @objid is null
    begin
		declare @curdbname sysname
	    select @curdbname = db_name()
	    raiserror(15009,-1,-1 ,@tabname, @curdbname)
	    return 1
    end
    --  (2) Must be a user table (and not a temp table)
    if ObjectProperty(@objid, 'IsUserTable') = 0 OR substring(parsename(@tabname,1),1,1) = '#'
    begin
	    raiserror(15218,-1,-1 ,@tabname)
	    return 1
    end

    -- CHECK PERMISSION ON TABLE --
    if (is_member('db_owner') = 0) AND (is_member('db_ddladmin') = 0)
        AND (is_member(user_name(ObjectProperty(@objid, 'ownerid'))) = 0)
    begin
        raiserror(15247,-1,-1)
        return 1
    end

	-- CHECK DATABASE MODE (must not be read-only) --
	if DATABASEPROPERTY(db_name(), 'IsReadOnly') = 1
	begin
		raiserror(15635, -1, -1, 'sp_fulltext_table')
		return 1
	end

	-- BEGIN TRAN AND LOCK TABLE --
	begin tran
	dbcc lockobjectschema(@tabname)
	if @@error <> 0
	begin
		goto error_abort_exit
	end

	-- OBTAIN CATALOG NAME FROM SYSOBJECTS & CHECK ACTION --
	declare @ftcatid smallint
	select @ftcatid = ObjectProperty(@objid, 'TableFulltextCatalogId')
	if @ftcatid <> 0 and @action = 'create'
	begin
        raiserror(15605,-1,-1,@tabname)
        goto error_abort_exit
	end
	if @ftcatid = 0 and @action <> 'create'
	begin
        raiserror(15606,-1,-1,@tabname)
        goto error_abort_exit
	end

	if @action = 'create'
	begin
		-- CHECK CATALOG NAME --
		select @ftcatid = null
		select @ftcatid = ftcatid from sysfulltextcatalogs where name = @ftcat
		if @ftcatid is null
		begin
			raiserror(7641,-1,-1,@ftcat)
			goto error_abort_exit
		end

		-- CHECK INDEX NAME (UNIQUE, SINGLE-KEY, 450-byte MAX, NON-NULLABLE) AND SET BIT IF FOUND --
		if IndexProperty(@objid, @keyname, 'IsUnique') = 1 and
		   IndexProperty(@objid, @keyname, 'UserKeyCount') = 1 and
		   IndexProperty(@objid, @keyname, 'IsHypothetical') = 0 and
		   exists (select * from syscolumns where id = @objid and name = Index_col(@tabname, IndexProperty(@objid, @keyname, 'IndexId'), 1)
				and length <= 450 and isnullable = 0)
		begin
			update sysindexes set status = status | 33554432 where id = @objid
				and name = @keyname and indid > 0 and indid < 255
		end
		else
		begin
			raiserror(15607,-1,-1,@keyname)
			goto error_abort_exit
		end

		-- ADD CATALOG NAME TO SYSOBJECTS --
		update sysobjects set ftcatid = @ftcatid where id = @objid

		-- ADD TO CATALOG
		DBCC CALLFULLTEXT ( 5, @ftcatid, @objid )	-- FTAddURL( @ftcatid, db_id(), @objid )
		if @@error <> 0
			goto error_abort_exit

	end

	if @action = 'drop'
	begin
		-- DROP FROM CATALOG (NO ERROR IF ALREADY DROPPED) --
		DBCC CALLFULLTEXT ( 6, @ftcatid, @objid )	-- FTDropURL( @ftcatid, db_id(), @objid )
		if @@error <> 0
			goto error_abort_exit

		-- REMOVE CATALOG NAME AND BITS FROM SYSTEM TABLES --
		update syscolumns set colstat = colstat & ~16 where id = @objid
		update sysindexes set status = status & ~33554432 where id = @objid
		update sysobjects set status = status & ~200, ftcatid = 0 where id = @objid

		-- DELETE SYSDEPENDS ENTRIES FOR IMAGE COLUMNS, IF ANY
		delete sysdepends where [id] = @objid and
								depid = @objid and
								deptype = 1

		-- DELETE NOTIFICATIONS FROM SYSFULLTEXTNOTIFY --
		delete sysfulltextnotify where tableid = @objid

	end

	if @action = 'activate'
	begin

		-- MUST HAVE AT LEAST ONE COLUMN MARKED FOR FULLTEXT INDEXING --
		if not exists (select * from syscolumns where id = @objid and (colstat & 16) = 16)
		begin
			raiserror(15609, -1,-1,@tabname)
			goto error_abort_exit
		end

		-- NO ERROR IF INDEXING ALREADY ACTIVATED --
		if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 1
		begin
			rollback tran
			return 0
		end


		update sysobjects set status = status | 8 where id = @objid

		if (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1)
		begin

			-- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
			if @@error <> 0
				goto error_abort_exit

			-- DELETE SYSFULLTEXTNOTIFY ENTRIES
			delete sysfulltextnotify where tableid = @objid

			-- START A FULL CRAWL FOR THE TABLE
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
			if @@error <> 0
				goto error_abort_exit

		end


	end

	if @action = 'deactivate'
	begin
		-- NO ERROR IF INDEXING ALREADY DEACTIVATED --
		if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0
		begin
			rollback tran
			return 0
		end

		-- IF TABLE IS NOT ENABLED FOR NOTIFICATIONS --
		if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0
		begin
			-- SET STATE TO INACTIVE, SCHEMA-MODIFIED
			update sysobjects set status = ((status & ~72) | 128) where id = @objid
		end
		else
		begin
			-- SET STATE TO INACTIVE
			update sysobjects set status = (status & ~8) where id = @objid
		end

		-- DELETE SYSFULLTEXTNOTIFY ENTRIES
		delete sysfulltextnotify where tableid = @objid

		-- STOP EXISTING CRAWL (IMPLICIT STOP WITH WARNING)
		DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
		if @@error <> 0
			goto error_abort_exit


	end

	if @action = 'start_change_tracking'
	begin
		-- ERROR IF TABLE IS NOT ACTIVATED --
		if (ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0)
		begin
	        raiserror(15630,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF TABLE IS ALREADY ENABLED FOR NOTIFICATIONS --
		if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1
		begin
			raiserror(15631,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF DATABASE IS IN SINGLE USER MODE --
		if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
		begin
			raiserror(15637, -1, -1, @tabname)
		    goto error_abort_exit
		end

		-- CHECK TO SEE IF THERE ARE ANY COLUMNS WHICH ARE NOT IN ROW BLOBS --
		if (select count(*) from syscolumns where
			(id = object_id(@tabname)) and ((xtype = 34) or (xtype = 35) or (xtype = 99)) and
			((colstat & 16) != 0) and (length = 16)) > 0
		begin
			raiserror(15639, -1, -1, @tabname)
		end

		-- STOP EXISTING CRAWL
		DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
		if @@error <> 0
			goto error_abort_exit

		-- DELETE SYSFULLTEXTNOTIFY ENTRIES
		delete sysfulltextnotify where tableid = @objid

		select @schemamodified = ObjectProperty(@objid, 'TableIsFulltextSchemaModified')

		-- SET TABLE TO CT ON. SCHEMA MOD. OFF --
		update sysobjects set status = ((status & ~128) | 64) where id = @objid

		-- COMMIT TRAN -- NESCESSARY TO TURN ON CT BEFORE CRAWL IS KICKED OFF --
		commit tran
		if @@error <> 0
			goto error_abort_exit

		if (@schemamodified = 1)
		begin
			-- START A FULL POPULATION FOR THIS TABLE --
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
			if @@error <> 0
			begin
				-- NEED TO RUN A FULL POPULATION
				raiserror(15644, -1, -1,@tabname, 'start_full')
				return 1
			end
		end
		else
		begin
			-- START AN INCREMENTAL POPULATION FOR THIS TABLE --
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 1 )
			if @@error <> 0
			begin
				-- NEED TO RUN AN INCREMENTAL POPULATION
				raiserror(15644, -1, -1, @tabname, 'start_incremental')
				return 1
			end
		end
		return 0

	end

	if @action = 'stop_change_tracking'
	begin
		-- ERROR IF TABLE IS NOT ACTIVATED --
		if (ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0)
		begin
	        raiserror(15630,-1,-1, @tabname)
		    goto error_abort_exit
		end

		if (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0)
		begin
		    rollback tran
	    	    return 0
		end

		if(ObjectProperty(@objid, 'TableFulltextPopulateStatus') != 0)
		begin
	        raiserror(7640,-1,-1, @tabname)	
		end

		-- DISABLE FULLTEXT AUTO PROPAGATION (NO ERROR IF ALREADY DISABLED) --
		DBCC CALLFULLTEXT ( 9, @objid )	-- FTDisableNotify( db_id(), @objid )
		if @@error <> 0
			goto error_abort_exit

		-- TURN OFF ACTIVE BITS IN SYSOBJECTS --
		update sysobjects set status = status & ~192 where id = @objid

		if ((select count(*) from sysfulltextnotify where tableid = @objid) != 0)
		begin
	        raiserror(7638,-1,-1, @tabname)
		end

		-- DELETE NOTIFICATIONS FROM SYSFULLTEXTNOTIFY --
		delete sysfulltextnotify where tableid = @objid


	end

	if @action = 'start_background_updateindex'
	begin
		-- ERROR IF TABLE IS NOT ACTIVATED --
		if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0
		begin
	        raiserror(15630,-1,-1, @tabname)
		    goto error_abort_exit
		end


		-- ERROR IF TABLE IS NOT ENABLED FOR NOTIFICATIONS --
		if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0
		begin
	        raiserror(15632,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF FULLTEXT SCHEMA OF THE TABLE HAS BEEN MODIFIED (SHOULD NEVER HAPPEN)--
		if (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 1)
		begin
	        raiserror(15640,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF TABLE IS ALREADY ENABLED FOR AUTO PROPAGATION --
		if ObjectProperty(@objid, 'TableFulltextBackgroundUpdateIndexOn') = 1
		begin
			raiserror(15633,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ENABLE TABLE FOR FULLTEXT AUTO PROPAGATION --
		DBCC CALLFULLTEXT ( 10, @ftcatid, @objid ) -- FTEnableAutoProp( @ftcatid, db_id(), @objid )
		if @@error <> 0
			goto error_abort_exit

		-- TURN ON FULLTEXT AUTOPROPAGATION BIT IN SYSOBJECTS --
		update sysobjects set status = status | 128 where id = @objid
	end

	if @action = 'stop_background_updateindex'
	begin
		-- ERROR IF TABLE IS NOT ACTIVATED --
		if (ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0)
		begin
	        raiserror(15630,-1,-1, @tabname)
		    goto error_abort_exit
		end

		if (ObjectProperty(@objid, 'TableBackgroundUpdateIndexOn') = 0)
		begin
		    rollback tran
		    return 0
		end

		-- DISABLE FULLTEXT AUTO PROPAGATION (NO ERROR IF ALREADY DISABLED) --
		DBCC CALLFULLTEXT ( 9, @objid )	-- FTDisableNotify( db_id(), @objid )
		if @@error <> 0
			goto error_abort_exit

		-- TURN OFF ACTIVE BITS IN SYSOBJECTS --
		update sysobjects set status = status & ~128 where id = @objid
	end

	if @action = 'update_index'
	begin

		-- ERROR IF TABLE IS NOT ENABLED FOR NOTIFICATIONS --
		if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0
		begin
	        raiserror(15634,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF TABLE IS NOT ACTIVE ANY MORE --
		if (ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0)
		begin
	        raiserror(15630,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF FULLTEXT SCHEMA OF THE TABLE HAS BEEN MODIFIED -- THIS SHOULD NEVER HAPPEN
		if (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 1)
		begin
	        raiserror(15640,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF DATABASE IS IN SIGNLE USER MODE --
		if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
		begin
			raiserror(15637, -1, -1, @tabname)
		    goto error_abort_exit
		end

		DBCC CALLFULLTEXT ( 11, @ftcatid, @objid )	-- FTStartPropagation( db_id(), @ftcatid, @objid )
		if @@error <> 0
			goto error_abort_exit
	end

	if @action = 'start_full'
	begin
		-- ERROR IF TABLE IS NOT ACTIVATED --
		if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0
		begin
	        raiserror(15630,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF DATABASE IS IN SINGLE USER MODE --
		if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
		begin
			raiserror(15637, -1, -1, @tabname)
		    goto error_abort_exit
		end

		-- RAISE WARNING IF POPULATE STATUS OF THE TABLE IS NOT IDLE
		if (ObjectProperty(@objid, 'TableFulltextPopulateStatus') != 0)
		begin
	        raiserror(7636,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- DELETE SYSFULLTEXTNOTIFY ENTRIES
		delete sysfulltextnotify where tableid = @objid

		-- START A FULL POPULATION FOR THIS TABLE --
		DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
		if @@error <> 0
			goto error_abort_exit

		if (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 1)
		and (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0)
			-- SET TABLE SCHEMA-UNMODIFIED
			update sysobjects set status = status & ~128 where id = @objid

	end

	if @action = 'start_incremental'
	begin
		-- ERROR IF TABLE IS NOT ACTIVATED --
		if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0
		begin
	        raiserror(15630,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF DATABASE IS IN SINGLE USER MODE --
		if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
		begin
			raiserror(15637, -1, -1, @tabname)
		    goto error_abort_exit
		end

		-- RAISE WARNING IF POPULATE STATUS OF THE TABLE IS NOT IDLE
		if (ObjectProperty(@objid, 'TableFulltextPopulateStatus') != 0)
		begin
	        raiserror(7636,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- DELETE SYSFULLTEXTNOTIFY ENTRIES
		delete sysfulltextnotify where tableid = @objid

		-- START AN INCREMENTAL POPULATION FOR THIS TABLE --
		if (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 1)
			and (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0)
			begin
			-- FULL CRAWL IF SCHEMA MODIFIED
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
			if @@error <> 0
				goto error_abort_exit

			-- SET TABLE SCHEMA-UNMODIFIED
			update sysobjects set status = status & ~128 where id = @objid
		end
		else
		begin
			-- INCREMENTAL CRAWL
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 1 )
			if @@error <> 0
				goto error_abort_exit
		end
	end

	if @action = 'stop'
	begin
		-- ERROR IF TABLE IS NOT ACTIVATED --
		if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0
		begin
	        raiserror(15630,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF POPULATE STATUS OF THE TABLE IS CRAWLING AND CT ON
		if (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1)
		and ((ObjectProperty(@objid, 'TableFulltextPopulateStatus') = 1)
		or (ObjectProperty(@objid, 'TableFulltextPopulateStatus') = 2))
		begin
		    raiserror(15642,-1,-1, @tabname)
			goto error_abort_exit
		end

		-- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
		DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
		if @@error <> 0
			goto error_abort_exit
	end

	-- COMMIT TRAN --
	commit tran
	if @@error <> 0
		goto error_abort_exit

	-- SUCCESS --
	return 0

error_abort_exit:
	rollback tran
	return 1	-- sp_fulltext_table
go

---------------------------- sp_fulltext_column -------------------------------

raiserror(15339,-1,-1,'sp_fulltext_column')
go
create proc sp_fulltext_column
    @tabname        nvarchar(517),      -- table name
    @colname        sysname,            -- column name
    @action         varchar(20),        -- add | drop
    @language       int = null,         -- LCID of data in the column
    @type_colname   sysname = null      -- column name, valid if colname is img

as
	-- FULLTEXT MUST BE ACTIVE IN DATABASE --
	if DatabaseProperty(db_name(), 'IsFulltextEnabled') = 0
	begin
		raiserror(15601,-1,-1)
		return 1
	end

	-- VALIDATE PARAMS --
	if @action is null or @action not in ('add','drop')
	begin
		raiserror(15600,-1,-1,'sp_fulltext_column')
		return 1
	end

	-- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	if @@trancount > 0
	begin
		raiserror(15002,-1,-1,'sp_fulltext_column')
		return 1
	end

	-- VALIDATE TABLE NAME --
	--	(1) Must exist in current database
	declare @objid int
	select @objid = object_id(@tabname, 'local')
	if @objid is null
	begin
		declare @curdbname sysname
		select @curdbname = db_name()
		raiserror(15009,-1,-1 ,@tabname, @curdbname)
		return 1
	end
	--	(2) Must be a user table
	if ObjectProperty(@objid, 'IsUserTable') = 0
	begin
		raiserror(15218,-1,-1 ,@tabname)
		return 1
	end

	-- CHECK PERMISSION ON TABLE --
	if (is_member('db_owner') = 0) AND (is_member('db_ddladmin') = 0)
		AND (is_member(user_name(ObjectProperty(@objid, 'ownerid'))) = 0)
	begin
		raiserror(15247,-1,-1)
		return 1
	end

	-- CHECK DATABASE MODE (must not be read-only) --
	if DATABASEPROPERTY(db_name(), 'IsReadOnly') = 1
	begin
		raiserror(15635, -1, -1, 'sp_fulltext_column')
		return 1
	end

	-- BEGIN TRAN AND LOCK TABLE --
	begin tran
	dbcc lockobjectschema(@tabname)
	if @@error <> 0
	begin
		goto error_abort_exit
	end

	-- CHECK FOR CATALOG IN SYSOBJECTS --
	declare @ftcatid smallint
	select @ftcatid = ObjectProperty(@objid, 'TableFulltextCatalogId')

	if @ftcatid = 0
	begin
		raiserror(15606,-1,-1,@tabname)
		goto error_abort_exit
	end

	-- VALIDATE COLUMN NAME (CANNOT BE COMPUTED) --
	declare @typename sysname
	select @typename = type_name(ColumnProperty(@objid, @colname, 'SystemType'))
	if @typename is null OR ColumnProperty(@objid, @colname, 'IsComputed') = 1
	begin
		raiserror(15104,-1,-1,@tabname,@colname)
		goto error_abort_exit
	end

	-- VALIDATE PARAMETERS
	if (@action <> 'add' or @typename <> N'image') and @type_colname is not null
	begin
		raiserror(15600, -1, -1, 'sp_fulltext_column')
		goto error_abort_exit
	end

	if @action = 'add'
	begin
		-- VALIDATE COLUMN TYPE --
		if @typename not in (N'nchar',N'nvarchar',N'ntext',N'char',N'varchar',N'text', N'image')
		begin
			raiserror(15611,-1,-1,@colname,@tabname)
			goto error_abort_exit
		end

		-- LANGUAGE
		if @language is null
			begin
				-- USE THE SERVER DEFAULT WORD BREAKING LANGUAGE
				select @language = value from master.dbo.syscurconfigs where config = 1126
			end
		else
			begin
				-- VALIDATE @LANGUAGE ARGUMENT
				if @language < 0
				begin
					raiserror(15600,-1,-1,'sp_fulltext_column')
					goto error_abort_exit
				end
			end

		update syscolumns set language = @language where id = @objid and name = @colname

		-- IF TABLE HAS ZERO INDEXED COLUMNS (THIS IS THE FIRST COLUMN TO BE ADDED), MARK IT ACTIVE
		if not exists (select * from syscolumns where id = @objid and (colstat & 16) = 16)
			and (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0)
			and (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 0)
		begin
			update sysobjects set status = (status  | 8) where id = @objid
		end

		-- SET THE BIT FOR THIS COLUMN --
		update syscolumns set colstat = colstat | 16 where id = @objid and name = @colname

		-- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
		DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
		if @@error <> 0
			goto error_abort_exit

		if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1
		begin


			if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 1
			begin

				-- DELETE SYSFULLTEXTNOTIFY ENTRIES
				delete sysfulltextnotify where tableid = @objid

				-- START A FULL CRAWL FOR THE TABLE
				DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
				if @@error <> 0
					goto error_abort_exit
			end

		end
		else
		begin

			-- SET STATE TO SCHEMA_MODIFIED
			update sysobjects set status = ((status & ~64) | 128) where id = @objid
		end

		if @typename = N'image'
		begin
			-- VALIDATE THAT THE TYPE COLUMN IS GIVEN AND THAT IT IS VALID
			if @type_colname is null
			begin
				raiserror(15600, -1, -1, 'sp_fulltext_column')
				goto error_abort_exit
			end

			declare @typecolname sysname
			select @typecolname = type_name(ColumnProperty(@objid, @type_colname, 'SystemType'))

			-- TYPE COLUMN HAS TO BE A CHARACTER COLUMN
			if @typecolname not in (N'nchar',N'nvarchar',N'char',N'varchar')
			begin
				raiserror(15600 , -1, -1, 'sp_fulltext_column')
				goto error_abort_exit
			end

			-- ADD ENTRY OF COLID IN SYSDEPENDS
			declare @colid smallint
			declare @type_colid smallint

			select @colid = colid from syscolumns where [id] = @objid and name = @colname
			select @type_colid = colid from syscolumns where [id]  = @objid and name = @type_colname

			if not exists ( select [id] from sysdepends
							where  [id] = @objid and
								   depid = @objid and
								   number = @colid )
			begin
				insert into sysdepends ([id], depid, number, depnumber, status, deptype )
						values( @objid, @objid, @colid, @type_colid, 0, 1)
			end

			-- SET BIT INDICATING TYPE COLUMN
			update syscolumns set colstat = colstat | 64 where id = @objid and name = @type_colname
		end

	end
	else
	begin
		-- CLEAR THE BIT FOR THIS COLUMN --
		update syscolumns set colstat = colstat & ~16 where id = @objid and name = @colname

		-- IF LAST COLUMN DROPPED
		if not exists (select * from syscolumns where id = @objid and (colstat & 16) = 16)
		begin
			-- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
			if @@error <> 0
				goto error_abort_exit

			-- IF TABLE HAS NOT BEEN DEACTIVATED
			if ((ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 1)
				or (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 0))
			begin

				-- IF CHANGE-TRACKING IS OFF
				if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0
				begin
					-- SET TABLE TO SCHEMA UNMODIFIED, INACTIVE (TURN OFF ALL BITS)
					update sysobjects set status = (status & ~200) where id = @objid
				end
				else
				begin
					-- SET TABLE TO INACTIVE
					update sysobjects set status = (status & ~8) where id = @objid
				end
			end
		end
		else
		if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1
		begin

			-- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
			if @@error <> 0
				goto error_abort_exit

			if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 1
			begin
				-- DELETE SYSFULLTEXTNOTIFY ENTRIES
				delete sysfulltextnotify where tableid = @objid

				-- START A FULL CRAWL FOR THE TABLE
				DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
				if @@error <> 0
					goto error_abort_exit
			end

		end
		else
		begin

			-- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
			if @@error <> 0
				goto error_abort_exit

			-- SET BITS IN SYSOBJECTS
			update sysobjects set status = ((status & ~64) | 128) where id = @objid
		end

		-- IF IMAGE COLUMN, UNBIND FROM THE TYPE COLUMN
		if @typename = N'image'
		begin
			declare @colid1 smallint
			declare @type_colid1 smallint

			select @colid1 = colid from syscolumns where [id] = @objid and name = @colname
			select @type_colid1 = depnumber from sysdepends
					where [id] = @objid and
						 depid = @objid and
						 number = @colid1

			delete sysdepends where [id] = @objid and
									depid = @objid and
									number = @colid1 and
									depnumber = @type_colid1 and
									deptype = 1

			-- CLEAR BIT RELATING THE IMAGE COLUMN AND TYPE COLUMN
			update syscolumns set colstat = colstat & ~64 where id = @objid and colid = @type_colid1
		end

	end

	-- COMMIT TRAN --
	commit tran
	if @@error <> 0
		goto error_abort_exit

	-- SUCCESS --
	return 0

error_abort_exit:
	rollback tran
	return 1	-- sp_fulltext_column
go

---------------------------- sp_help_fulltext_catalogs ------------------------------

raiserror(15339,-1,-1,'sp_help_fulltext_catalogs')
go
create proc sp_help_fulltext_catalogs
	@fulltext_catalog_name		sysname = NULL		-- full-text catalog name
as
	-- FULLTEXT MUST BE ACTIVE IN DATABASE --
	if DatabaseProperty(db_name(), 'IsFulltextEnabled') = 0
	begin
		raiserror(15601,-1,-1)
		return 1
	end

	-- CATALOG MUST EXIST IF SPECIFIED --
	if @fulltext_catalog_name is not null
	begin
		declare @ftcatid smallint
		select @ftcatid = ftcatid from sysfulltextcatalogs where name = @fulltext_catalog_name
		if @ftcatid is null
		begin
			raiserror(7641,-1,-1,@fulltext_catalog_name)
			return 1
		end
	end

	-- RETRIEVE THE DEFAULT PATH --
	DECLARE @def_path as nvarchar(260)
	select @def_path = null
	exec master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE',
		'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer','FullTextDefaultPath',
		@def_path OUT

	-- SELECT ANY ROWS THAT MEET THE CRITERIA --
	select CAT.ftcatid,
	   name as NAME,
		   'PATH'= CASE WHEN path is NULL THEN @def_path
						ELSE path
						END,
		   FullTextCatalogProperty(CAT.name, 'PopulateStatus') AS STATUS ,
		   (select COUNT(*)
			  from sysobjects
			  where type='U' and sysobjects.ftcatid = CAT.ftcatid
		   ) as NUMBER_FULLTEXT_TABLES
	from sysfulltextcatalogs as CAT
	where ( @fulltext_catalog_name is null or name = @fulltext_catalog_name )
	order by ftcatid

	-- SUCCESS --
	return 0	-- sp_help_fulltext_catalogs
go

---------------------------- sp_help_fulltext_catalogs_cursor ------------------------------

raiserror(15339,-1,-1,'sp_help_fulltext_catalogs_cursor')
go
create proc sp_help_fulltext_catalogs_cursor
	@cursor_return CURSOR VARYING OUTPUT,
	@fulltext_catalog_name		sysname = NULL		-- full-text catalog name
as
	-- FULLTEXT MUST BE ACTIVE IN DATABASE --
	if DatabaseProperty(db_name(), 'IsFulltextEnabled') = 0
	begin
		raiserror(15601,-1,-1)
		return 1
	end

	-- CATALOG MUST EXIST IF SPECIFIED --
	if @fulltext_catalog_name is not null
	begin
		declare @ftcatid smallint
		select @ftcatid = ftcatid from sysfulltextcatalogs where name = @fulltext_catalog_name
		if @ftcatid is null
		begin
			raiserror(7641,-1,-1,@fulltext_catalog_name)
			return 1
		end
	end

	-- RETRIEVE THE DEFAULT PATH --
	DECLARE @def_path as nvarchar(260)
	select @def_path = null
	exec master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE',
		'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer','FullTextDefaultPath',
		@def_path OUT

	-- SELECT ANY ROWS THAT MEET THE CRITERIA --
	set @cursor_return =	CURSOR LOCAL SCROLL DYNAMIC FOR
	select CAT.ftcatid,
	   name as NAME,
	   'PATH'= CASE WHEN path is NULL THEN @def_path
							ELSE path
							END,
		FullTextCatalogProperty(CAT.name, 'PopulateStatus') AS STATUS ,
		(select COUNT(*)
		 from sysobjects
		 where type='U' and sysobjects.ftcatid = CAT.ftcatid
		 ) as NUMBER_FULLTEXT_TABLES
		from sysfulltextcatalogs as CAT
		where ( @fulltext_catalog_name is null or name = @fulltext_catalog_name )
		order by ftcatid

	open @cursor_return

	-- SUCCESS --
	return 0	-- sp_help_fulltext_catalogs_cursor
go

---------------------------- sp_help_fulltext_tables ------------------------------

raiserror(15339,-1,-1,'sp_help_fulltext_tables')
go
create proc sp_help_fulltext_tables
	@fulltext_catalog_name		sysname = NULL, 		-- full-text catalog name
	@table_name nvarchar(517) = NULL	-- table name
as
	-- FULLTEXT MUST BE ACTIVE IN DATABASE --
	if DatabaseProperty(db_name(), 'IsFulltextEnabled') = 0
	begin
		raiserror(15601,-1,-1)
		return 1
	end

	-- CATALOG MUST EXIST IF SPECIFIED --
	declare @ftcatid smallint
	if @fulltext_catalog_name is not null
	begin
		select @ftcatid = ftcatid from sysfulltextcatalogs where name = @fulltext_catalog_name
		if @ftcatid is null
		begin
			raiserror(7641,-1,-1,@fulltext_catalog_name)
			return 1
		end
	end

	if @table_name is not null
	begin
		-- VALIDATE TABLE NAME --
		--	(1) Must exist in current database
		declare @objid int
		select @objid = object_id(@table_name, 'local')
		if @objid is null
		begin
			declare @curdbname sysname
			select @curdbname = db_name()
			raiserror(15009,-1,-1 ,@table_name, @curdbname)
			return 1
		end
		--	(2) Must be a user table (and not a temp table)
		if ObjectProperty(@objid, 'IsUserTable') = 0 OR substring(parsename(@table_name,1),1,1) = '#'
		begin
			raiserror(15218,-1,-1 ,@table_name)
			return 1
		end
	end

	select susr.name as TABLE_OWNER, sobj.name as TABLE_NAME,
		sdex.name as FULLTEXT_KEY_INDEX_NAME,
		ObjectProperty(sobj.id, 'TableFulltextKeyColumn') as FULLTEXT_KEY_COLID,
		ObjectProperty(sobj.id, 'TableHasActiveFulltextIndex') as FULLTEXT_INDEX_ACTIVE,
		scat.name as FULLTEXT_CATALOG_NAME
		from sysobjects as sobj, sysindexes as sdex, sysusers as susr, sysfulltextcatalogs as scat
		where(
				@fulltext_catalog_name is null or
				sobj.ftcatid = @ftcatid
			 ) and
			 (
				@table_name is null or
				sobj.id = @objid
			 ) and
			  sobj.uid = susr.uid and
			  sobj.ftcatid = scat.ftcatid and
			  sdex.status & 33554432 <> 0 and	/* means that this is the index used enforce
												   the uniqueness of the full-text key column */
			  sdex.id = sobj.id
		order by TABLE_OWNER, TABLE_NAME

	-- SUCCESS --
	return 0	-- sp_help_fulltext_tables
go

---------------------------- sp_help_fulltext_tables_cursor ------------------------------
raiserror(15339,-1,-1,'sp_help_fulltext_tables_cursor')
go
create proc sp_help_fulltext_tables_cursor
	@cursor_return CURSOR VARYING OUTPUT,
	@fulltext_catalog_name		sysname = NULL, 		-- full-text catalog name
	@table_name nvarchar(517) = NULL	-- table name
as
	-- FULLTEXT MUST BE ACTIVE IN DATABASE --
	if DatabaseProperty(db_name(), 'IsFulltextEnabled') = 0
	begin
		raiserror(15601,-1,-1)
		return 1
	end

	-- CATALOG MUST EXIST IF SPECIFIED --
	declare @ftcatid smallint
	if @fulltext_catalog_name is not null
	begin
		select @ftcatid = ftcatid from sysfulltextcatalogs where name = @fulltext_catalog_name
		if @ftcatid is null
		begin
			raiserror(7641,-1,-1,@fulltext_catalog_name)
			return 1
		end
	end

	if @table_name is not null
	begin
		-- VALIDATE TABLE NAME --
		--	(1) Must exist in current database
		declare @objid int
		select @objid = object_id(@table_name, 'local')
		if @objid is null
		begin
			declare @curdbname sysname
			select @curdbname = db_name()
			raiserror(15009,-1,-1 ,@table_name, @curdbname)
			return 1
		end
		--	(2) Must be a user table (and not a temp table)
		if ObjectProperty(@objid, 'IsUserTable') = 0
						 OR substring(parsename(@table_name,1),1,1) = '#'
		begin
			raiserror(15218,-1,-1 ,@table_name)
			return 1
		end
	end

	set @cursor_return =	CURSOR LOCAL SCROLL DYNAMIC FOR
	select susr.name as TABLE_OWNER, sobj.name as TABLE_NAME,
		sdex.name as FULLTEXT_KEY_INDEX_NAME,
		ObjectProperty(sobj.id, 'TableFulltextKeyColumn') as FULLTEXT_KEY_COLID,
		ObjectProperty(sobj.id, 'TableHasActiveFulltextIndex') as FULLTEXT_INDEX_ACTIVE,
		scat.name as FULLTEXT_CATALOG_NAME
		from sysobjects as sobj, sysindexes as sdex, sysusers as susr, sysfulltextcatalogs as scat
		where(
				@fulltext_catalog_name is null or
				sobj.ftcatid = @ftcatid
			 ) and
			 (
				@table_name is null or
				sobj.id = @objid
			 ) and
			  sobj.uid = susr.uid and
			  sobj.ftcatid = scat.ftcatid and
			  sdex.status & 33554432 <> 0 and	/* means that this is the index used enforce
												   the uniqueness of the full-text key column */
			  sdex.id = sobj.id
		order by TABLE_OWNER, TABLE_NAME

	open @cursor_return

	-- SUCCESS --
	return 0	-- sp_help_fulltext_tables_cursor
go

---------------------------- sp_help_fulltext_columns ------------------------------
raiserror(15339,-1,-1,'sp_help_fulltext_columns')
go
create proc sp_help_fulltext_columns
	@table_name nvarchar(517) = NULL,		-- table name
	@column_name	sysname = NULL			-- column name
as
	-- FULLTEXT MUST BE ACTIVE IN DATABASE --
	if DatabaseProperty(db_name(), 'IsFulltextEnabled') = 0
	begin
		raiserror(15601,-1,-1)
		return 1
	end

	if @table_name is not null
	begin
		-- VALIDATE TABLE NAME --
		--	(1) Must exist in current database
		declare @objid int
		select @objid = object_id(@table_name, 'local')
		if @objid is null
		begin
			declare @curdbname sysname
			select @curdbname = db_name()
			raiserror(15009,-1,-1 ,@table_name, @curdbname)
			return 1
		end
		--	(2) Must be a user table (and not a temp table)
		if ObjectProperty(@objid, 'IsUserTable') = 0
							 OR substring(parsename(@table_name,1),1,1) = '#'
		begin
			raiserror(15218,-1,-1 ,@table_name)
			return 1
		end

		-- VALIDATE COLUMN NAME (CANNOT BE COMPUTED) --
		if @column_name is not null
		begin
			declare @typename sysname
			select @typename = type_name(ColumnProperty(@objid, @column_name, 'SystemType'))
			if @typename is null OR ColumnProperty(@objid, @column_name, 'IsComputed') = 1
			begin
				raiserror(15104,-1,-1,@table_name,@column_name)
				return 1
			end
		end

	end

	select distinct
		susr.name as TABLE_OWNER,
		sobj.id as TABLE_ID,
		sobj.name as TABLE_NAME,
		scol.name as FULLTEXT_COLUMN_NAME,
		scol.colid as FULLTEXT_COLID,
		b.FT_BLOBTPNAME as FULLTEXT_BLOBTP_COLNAME,
		a.FT_BLOBTPCOLID as FULLTEXT_BLOBTP_COLID,
		scol.language as FULLTEXT_LANGUAGE
	from
		sysobjects as sobj,
 		sysusers as susr,
		syscolumns as scol
		left outer join
			(
			select 	sdep.id			as TABLE_ID,
					sdep.number		as FULLTEXT_COLID,
					sdep.depnumber	as FT_BLOBTPCOLID
			from
					sysdepends as sdep,
					syscolumns as scol
			where
					scol.colid = sdep.number
				and scol.id = sdep.id
				and sdep.deptype = 1
				and ColumnProperty(scol.id, scol.name, 'IsFullTextIndexed') = 1
			) as a
		on (scol.colid = a.FULLTEXT_COLID and scol.id = a.TABLE_ID)
		left outer join
			(
			select 	sdep.id			as TABLE_ID,
					sdep.depnumber	as FT_BLOBTPCOLID,
					scol.name		as FT_BLOBTPNAME,
					sdep.number		as FULLTEXT_COLID
			from
					syscolumns as scol,
					sysdepends as sdep
			where
					scol.colid = sdep.depnumber
				and ColumnProperty(sdep.id, scol.name, 'IsTypeForFullTextBlob') = 1
			) as b
		on (a.FULLTEXT_COLID = b.FULLTEXT_COLID and a.TABLE_ID = b.TABLE_ID)
	where (
			@table_name is null or
			sobj.id = @objid
		  ) and
		  scol.id = sobj.id and
		  sobj.uid = susr.uid and
		  (
			  @column_name is null or
			  scol.name = @column_name
		  ) and
		  ColumnProperty(sobj.id, scol.name, 'IsFullTextIndexed') = 1
	order by TABLE_OWNER, TABLE_NAME, FULLTEXT_COLID

	-- SUCCESS --
	return 0	-- sp_help_fulltext_columns
go

---------------------------- sp_help_fulltext_columns_cursor ------------------------------
raiserror(15339,-1,-1,'sp_help_fulltext_columns_cursor')
go
create proc sp_help_fulltext_columns_cursor
	@cursor_return CURSOR VARYING OUTPUT,
	@table_name nvarchar(517) = NULL,		-- table name
	@column_name	sysname = NULL				-- column name
as
	-- FULLTEXT MUST BE ACTIVE IN DATABASE --
	if DatabaseProperty(db_name(), 'IsFulltextEnabled') = 0
	begin
		raiserror(15601,-1,-1)
		return 1
	end

	if @table_name is not null
	begin
		-- VALIDATE TABLE NAME --
		--	(1) Must exist in current database
		declare @objid int
		select @objid = object_id(@table_name, 'local')
		if @objid is null
		begin
			declare @curdbname sysname
			select @curdbname = db_name()
			raiserror(15009,-1,-1 ,@table_name, @curdbname)
			return 1
		end
		--	(2) Must be a user table (and not a temp table)
		if ObjectProperty(@objid, 'IsUserTable') = 0
								 OR substring(parsename(@table_name,1),1,1) = '#'
		begin
			raiserror(15218,-1,-1 ,@table_name)
			return 1
		end

		-- VALIDATE COLUMN NAME (CANNOT BE COMPUTED) --
		if @column_name is not null
		begin
			declare @typename sysname
			select @typename = type_name(ColumnProperty(@objid, @column_name, 'SystemType'))
			if @typename is null OR ColumnProperty(@objid, @column_name, 'IsComputed') = 1
			begin
				raiserror(15104,-1,-1,@table_name,@column_name)
				return 1
			end
		end
	end

	set @cursor_return =	CURSOR LOCAL SCROLL DYNAMIC FOR
		select distinct
			susr.name as TABLE_OWNER,
			sobj.id as TABLE_ID,
			sobj.name as TABLE_NAME,
			scol.name as FULLTEXT_COLUMN_NAME,
			scol.colid as FULLTEXT_COLID,
			b.FT_BLOBTPNAME as FULLTEXT_BLOBTP_COLNAME,
			a.FT_BLOBTPCOLID as FULLTEXT_BLOBTP_COLID,
			scol.language as FULLTEXT_LANGUAGE
		from
			sysobjects as sobj,
			sysusers as susr,
			syscolumns as scol
			left outer join
				(
				select 	sdep.id			as TABLE_ID,
						sdep.number		as FULLTEXT_COLID,
						sdep.depnumber	as FT_BLOBTPCOLID
				from
						sysdepends as sdep,
						syscolumns as scol
				where
						scol.colid = sdep.number
					and scol.id = sdep.id
					and sdep.deptype = 1
					and ColumnProperty(scol.id, scol.name, 'IsFullTextIndexed') = 1
				) as a
			on (scol.colid = a.FULLTEXT_COLID and scol.id = a.TABLE_ID)
			left outer join
				(
				select 	sdep.id			as TABLE_ID,
						sdep.depnumber	as FT_BLOBTPCOLID,
						scol.name		as FT_BLOBTPNAME,
						sdep.number		as FULLTEXT_COLID
				from
						syscolumns as scol,
						sysdepends as sdep
				where
						scol.colid = sdep.depnumber
					and ColumnProperty(sdep.id, scol.name, 'IsTypeForFullTextBlob') = 1
				) as b
			on (a.FULLTEXT_COLID = b.FULLTEXT_COLID and a.TABLE_ID = b.TABLE_ID)
		where (
				@table_name is null or
				sobj.id = @objid
			  ) and
			  scol.id = sobj.id and
			  sobj.uid = susr.uid and
			  (
				  @column_name is null or
				  scol.name = @column_name
			  ) and
			  ColumnProperty(sobj.id, scol.name, 'IsFullTextIndexed') = 1
		order by TABLE_OWNER, TABLE_NAME, FULLTEXT_COLID

	open @cursor_return

	-- SUCCESS --
	return 0	-- sp_help_fulltext_columns_cursor
go

-- GRANT PUBLIC ACCESS --
grant execute on sp_fulltext_service to public
grant execute on sp_fulltext_database to public
grant execute on sp_fulltext_catalog to public
grant execute on sp_fulltext_table to public
grant execute on sp_fulltext_column to public
grant execute on sp_help_fulltext_catalogs to public
grant execute on sp_help_fulltext_catalogs_cursor to public
grant execute on sp_help_fulltext_tables  to public
grant execute on sp_help_fulltext_tables_cursor  to public
grant execute on sp_help_fulltext_columns  to public
grant execute on sp_help_fulltext_columns_cursor  to public
-- sp_fulltext_getdata is NOT for CUSTOMER USE! (do not doc)
go

/*************************  END FULLTEXT INDEXES *****************************/

checkpoint
go

/**********************  SQLTRACE STORED PROCEDURES **************************/

if object_id('sp_trace_getdata','P') IS NOT NULL
	drop procedure sp_trace_getdata
go

raiserror(15339,-1,-1,'sp_trace_getdata')
go
create procedure sp_trace_getdata
	(@traceid int,
	 @records int = 0
	)
as

select * from OpenRowset(TrcData, @traceid, @records)
go 

/*********************** END SQLTRACE PROCEDURES *****************************/

checkpoint
go


if object_id('sp_describe_cursor','P') IS NOT NULL
	drop procedure sp_describe_cursor
if object_id('sp_describe_cursor_columns','P') IS NOT NULL
	drop procedure sp_describe_cursor_columns
if object_id('sp_describe_cursor_tables','P') IS NOT NULL
	drop procedure sp_describe_cursor_tables
if object_id('sp_cursor_list','P') IS NOT NULL
	drop procedure sp_cursor_list
go

raiserror(15339,-1,-1,'sp_describe_cursor')
go
-- Creation of sp_describe_cursor

Create Procedure sp_describe_cursor
(  @cursor_return CURSOR VARYING OUTPUT,
   @cursor_source nvarchar (30),
   @cursor_identity nvarchar (128)
)
AS

declare @scope int

/* Check if the cursor exists by name or handle. */
If cursor_status ( @cursor_source, @cursor_identity ) >= -1
begin
	if lower(convert(varchar(30), @cursor_source)) = 'local' OR
		lower(convert(varchar(128), @cursor_source)) = 'variable'
		select @scope = 1
	else
	if lower(convert(varchar(30), @cursor_source)) = 'global'
		select @scope = 2


	set @cursor_return =  CURSOR LOCAL SCROLL DYNAMIC FOR
			    	SELECT reference_name, cursor_name, cursor_scope,
					status, model, concurrency, scrollable,
					open_status, cursor_rows, fetch_status,
					column_count, row_count, last_operation,
					cursor_handle
			    	FROM master.dbo.syscursorrefs scr, master.dbo.syscursors sc
			    	WHERE 	scr.cursor_scope = @scope and
				  	scr.reference_name = @cursor_identity and
				  	scr.cursor_handl = sc.cursor_handle
				ORDER BY 3, 1
				FOR READ ONLY
	open @cursor_return

end
go

raiserror(15339,-1,-1,'sp_describe_cursor_columns')
go
-- Creation of sp_describe_cursor_columns

Create Procedure sp_describe_cursor_columns
(  @cursor_return CURSOR VARYING OUTPUT,
   @cursor_source nvarchar (30),
   @cursor_identity nvarchar (128)
)
AS

declare @scope int

/* Check if the cursor exists by name or handle. */
If cursor_status ( @cursor_source, @cursor_identity ) >= -1
begin
	if lower(convert(varchar(30), @cursor_source)) = 'local' OR
		lower(convert(varchar(128), @cursor_source)) = 'variable'
		select @scope = 1
	else
	if lower(convert(varchar(30), @cursor_source)) = 'global'
		select @scope = 2

	set @cursor_return =  	CURSOR LOCAL SCROLL DYNAMIC FOR
				SELECT column_name, ordinal_position, column_characteristics_flags,
					column_size, data_type_sql, column_precision,
					column_scale, order_position, order_direction,
					hidden_column, columnid, objectid, dbid, dbname
				FROM master.dbo.syscursorrefs scr, master.dbo.syscursorcolumns scc
				WHERE 	scr.cursor_scope = @scope and
					scr.reference_name = @cursor_identity and
					scr.cursor_handl = scc.cursor_handle
				ORDER BY 2
				FOR READ ONLY
	open @cursor_return

end
go

raiserror(15339,-1,-1,'sp_describe_cursor_tables')
go
-- Creation of sp_describe_cursor_tables

Create Procedure sp_describe_cursor_tables
(  @cursor_return CURSOR VARYING OUTPUT,
   @cursor_source nvarchar (30),
   @cursor_identity nvarchar (128)
)
AS

declare @scope int

/* Check if the cursor exists by name or handle. */
If cursor_status ( @cursor_source, @cursor_identity ) >= -1
begin
	if lower(convert(varchar(30), @cursor_source)) = 'local' OR
		lower(convert(varchar(128), @cursor_source)) = 'variable'
		select @scope = 1
	else
	if lower(convert(varchar(30), @cursor_source)) = 'global'
		select @scope = 2

	set @cursor_return =  	CURSOR LOCAL SCROLL DYNAMIC FOR
				SELECT table_owner, table_name, optimizer_hint, lock_type, server_name, objectid, dbid, dbname
				FROM master.dbo.syscursorrefs scr, master.dbo.syscursortables sct
				WHERE 	scr.cursor_scope = @scope and
					scr.reference_name = @cursor_identity and
					scr.cursor_handl = sct.cursor_handle
				FOR READ ONLY
	open @cursor_return
end
go

raiserror(15339,-1,-1,'sp_cursor_list')
go
-- Creation of sp_cursor_list

create procedure sp_cursor_list
(
   @cursor_return CURSOR VARYING OUTPUT,
   @cursor_scope int
)
AS

if (@cursor_scope < 1) OR (@cursor_scope > 3)
	begin
		/* Raise an error: ?The value of parameter  is invalid? */
		raiserror ( 16902, 1, 1,N'sp_cursor_list', N'@cursor_scope')
		return (1)
	end

if ( @cursor_scope  < 3)
begin
	set @cursor_return =  CURSOR LOCAL SCROLL DYNAMIC FOR
			    	SELECT reference_name, cursor_name, cursor_scope,
					status, model, concurrency, scrollable,
					open_status, cursor_rows, fetch_status,
					column_count, row_count, last_operation,
					cursor_handle
				FROM master.dbo.syscursorrefs scr, master.dbo.syscursors sc
				WHERE 	scr.cursor_scope = @cursor_scope AND
					scr.cursor_handl = sc.cursor_handle
				FOR READ ONLY
end
else
begin
	set @cursor_return =  CURSOR LOCAL SCROLL DYNAMIC FOR
			    	SELECT reference_name, cursor_name, cursor_scope,
					status, model, concurrency, scrollable,
					open_status, cursor_rows, fetch_status,
					column_count, row_count, last_operation,
					cursor_handle
				FROM master.dbo.syscursorrefs scr, master.dbo.syscursors sc
				WHERE scr.cursor_handl = sc.cursor_handle
				FOR READ ONLY
end
open @cursor_return
go

/************************  END-CURSORINFO-STORED-PROCS  ***********************/

-- GRANT PUBLIC ACCESS --
grant execute on sp_describe_cursor to public
grant execute on sp_describe_cursor_columns to public
grant execute on sp_describe_cursor_tables to public
grant execute on sp_cursor_list to public
go

checkpoint
go

print ' '
print 'Granting privileges on system tables.'
go

grant select on syscharsets to public
grant select on syscolumns to public
grant select on syscomments to public
grant select on sysconfigures to public
grant select on sysdatabases to public
grant select on sysdepends to public
grant select on sysdevices to public
grant select on sysindexes to public
grant select on syslanguages to public
go


checkpoint
go

grant select on sysmessages to public
grant select on sysobjects to public
grant select on sysprotects to public
grant select on syspermissions to public
grant select on syssegments to public
grant select on sysservers to public
grant select on systypes to public
grant select on sysusers to public
grant select on sysreferences to public
grant select on sysfiles to public
grant select on sysfilegroups to public
grant select on sysfulltextcatalogs to public
go


checkpoint
go

print ' '
print 'Granting privileges on system stored procedures'
go

grant execute on MS_sqlctrs_users to public
go

grant execute on sp_user_counter1   to public
grant execute on sp_user_counter2   to public
grant execute on sp_user_counter3   to public
grant execute on sp_user_counter4   to public
grant execute on sp_user_counter5   to public
grant execute on sp_user_counter6   to public
grant execute on sp_user_counter7   to public
grant execute on sp_user_counter8   to public
grant execute on sp_user_counter9   to public
grant execute on sp_user_counter10  to public

checkpoint
go

grant execute on sp_addmessage to public
grant execute on sp_addumpdevice to public
grant execute on sp_addtype to public
grant execute on sp_altermessage to public
grant execute on sp_bindefault to public
grant execute on sp_bindrule to public
grant execute on sp_checknames to public
grant execute on sp_configure to public
grant execute on sp_cursor to public
grant execute on sp_cursorclose to public
grant execute on sp_cursorfetch to public
grant execute on sp_cursoropen to public
grant execute on sp_cursoroption to public
grant execute on sp_dboption to public
grant execute on sp_bindsession to public
grant execute on sp_getbindtoken to public
grant execute on sp_dbcmptlevel to public
grant execute on sp_executesql to public
grant execute on sp_prepare to public
grant execute on sp_execute to public
grant execute on sp_prepexec to public
grant execute on sp_prepexecrpc to public
grant execute on sp_unprepare to public
grant execute on sp_cursorprepare to public
grant execute on sp_cursorexecute to public
grant execute on sp_cursorprepexec to public
grant execute on sp_cursorunprepare to public
grant execute on sp_createorphan to public
grant execute on sp_droporphans to public
grant execute on sp_reset_connection to public
grant execute on sp_getschemalock to public
grant execute on sp_releaseschemalock to public
grant execute on sp_xml_preparedocument to public
grant execute on sp_xml_removedocument to public
go

checkpoint
go
grant execute on sp_diskdefault to public
grant execute on sp_dropdevice to public

grant execute on sp_depends to public
grant execute on sp_dropmessage to public
grant execute on sp_droptype to public
go
grant execute on sp_help to public
grant execute on sp_helpconstraint to public
grant execute on sp_helpdb to public
grant execute on sp_helpfile to public
grant execute on sp_helpfilegroup to public
grant execute on sp_helpdevice to public
grant execute on sp_helpextendedproc to public
grant execute on sp_helpgroup to public
grant execute on sp_helpindex to public
grant execute on sp_helpstats to public
grant execute on sp_helplanguage to public
grant execute on sp_helplog to public
grant execute on sp_helplogins to public
grant execute on sp_helpremotelogin to public
grant execute on sp_helprotect to public
grant execute on sp_helpsort to public
grant execute on sp_helpsql to public
grant execute on sp_helptext to public
grant execute on sp_helpuser to public
grant execute on sp_lock to public
grant execute on sp_getapplock to public
grant execute on sp_releaseapplock to public
grant select on system_function_schema.fn_helpcollations to public
grant select on system_function_schema.fn_servershareddrives to public
grant select on system_function_schema.fn_virtualfilestats to public
grant select on system_function_schema.fn_virtualservernodes to public

go

checkpoint
go

grant execute on sp_objectfilegroup to public
grant execute on sp_procoption to public
grant execute on sp_recompile to public
grant execute on sp_remoteoption to public
grant execute on sp_rename to public
grant execute on sp_renamedb to public
grant execute on sp_spaceused to public
grant execute on sp_sqlexec to public
go

grant execute on sp_blockcnt to public
grant execute on sp_tableoption to public
grant execute on sp_invalidate_textptr to public
grant execute on sp_indexoption to public
grant execute on sp_tempdbspace to public
go

grant execute on sp_unbindefault to public
grant execute on sp_unbindrule to public
grant execute on sp_validname to public
grant execute on sp_validlang to public
grant execute on sp_who to public
grant execute on sp_who2 to public
grant execute on sp_updatestats to public
grant execute on sp_createstats to public
grant execute on sp_autostats to public
grant execute on sp_helptrigger to public
grant execute on sp_addextendedproperty to public
grant execute on sp_updateextendedproperty to public
grant execute on sp_dropextendedproperty to public
grant execute on sp_addremotelogin to public
grant execute on sp_dropremotelogin to public
go

--grant select on system function
grant select on system_function_schema.fn_listextendedproperty to public
go

-- grant access to access resync query sps
grant execute on sp_resyncprepare to public
grant execute on sp_resyncexecute to public
grant execute on sp_resyncexecutesql to public
grant execute on sp_resyncuniquetable to public

checkpoint
go

print ' '
print 'Granting privileges on extended stored procedures'
go

grant execute on xp_sprintf to public
grant execute on xp_sscanf to public
grant execute on xp_msver to public
go

print ' '
print 'Granting privileges on objects in model database.'
go
use model
go
grant select on sysconstraints to public
grant select on syscolumns to public
grant select on syscomments to public
grant select on sysdepends to public
grant select on sysindexes to public
grant select on sysobjects to public
grant select on sysprotects to public
grant select on syspermissions to public
grant select on syssegments to public
grant select on systypes to public
grant select on sysusers to public
grant select on sysreferences to public
grant select on sysfiles to public
grant select on sysfilegroups to public
grant select on sysfulltextcatalogs to public
go

use master
go

checkpoint
go

/*
** Set some things now that sp_configure has been created.
*/
print ' '
print 'Making final database configuration settings.'
go

print 'Forcing config for remote access to 1'
exec sp_configure 'remote access',1
go

exec sp_MS_upd_sysobj_category 2  --Now do catalog updates.

go

--- Enable auto-create and update of statistics
exec sp_dboption 'tempdb', 'auto create statistics', 'on'
exec sp_dboption 'master', 'auto create statistics', 'on'
exec sp_dboption 'model', 'auto create statistics', 'on'
go
exec sp_dboption 'tempdb', 'auto update statistics', 'on'
exec sp_dboption 'master', 'auto update statistics', 'on'
exec sp_dboption 'model', 'auto update statistics', 'on'
go
exec sp_dboption 'master', 'torn page detection', 'on'
exec sp_dboption 'model', 'torn page detection', 'on'
go

exec sp_dboption 'master','trunc. log on chkpt.','true'
go
exec sp_configure 'allow updates',0
reconfigure with override

print ' '
-- print 'Checking objects created by ProcSyst.SQL ....'
go

declare @vdt varchar(99)
select  @vdt = convert(varchar,getdate(),113)
raiserror('Finishing Install\ProcSyst.SQL at  %s',0,1,@vdt)
go

checkpoint
go
-- - -----

-- test
-- junk
