windows-server-2003/sdktools/debuggers/oca/dbgportal/sql/auth_sps.sql

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddRestrictedCabAccess]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AddRestrictedCabAccess]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddUserAndLevel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AddUserAndLevel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddUserForApproval]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AddUserForApproval]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddUserLevel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AddUserLevel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ApproveUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ApproveUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CheckApprovalAccess]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CheckApprovalAccess]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CheckRestrictedCabAccess]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CheckRestrictedCabAccess]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CheckUserAccess]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CheckUserAccess]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CheckUserAccessApprovals]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CheckUserAccessApprovals]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DefragIndexes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DefragIndexes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetUserID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetUserID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OcaAddUserAndLevel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[OcaAddUserAndLevel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OcaAddUserForApproval]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[OcaAddUserForApproval]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OcaAddUserLevel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[OcaAddUserLevel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OcaApproveUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[OcaApproveUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OcaCheckUserAccess]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[OcaCheckUserAccess]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OcaCheckUserAccessApprovals]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[OcaCheckUserAccessApprovals]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OcaGetUserID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[OcaGetUserID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OcaRemoveUserLevel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[OcaRemoveUserLevel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RemoveUserLevel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[RemoveUserLevel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TestProc]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rolemember]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rolemember]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[updatestats]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[updatestats]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE AddRestrictedCabAccess
	@User varchar(50),
	@Domain varchar(50),
	@iDatabase int,
	@iBucket int,
	@szCabFilename varchar(100),
	@szCabPath   varchar(255)

As
	set nocount on
	-- local var to hold record id
	declare @target int

	declare @userfound int
	declare @userApproved int
	declare @CabAccessID int


	SELECT @userfound = UserID FROM AuthorizedUsers WHERE UserAlias = @User and UserDomain = @Domain


	if @userfound is NULL
		begin
		select @target = -1
		return @target
		end

	SELECT @userApproved = UserID FROM Approvals WHERE UserID = @userfound

	if @userApproved is NULL
		begin	
		select @target = -1
		return @target

		end

	SELECT @CabAccessID= CabAccessID from CabAccess WHERE UserID = @userfound and iBucket=@iBucket and iDatabase=@iDatabase 
		and CabFilename=@szCabFilename and CabPath=@szCabPath

	if @CabAccessID is NULL
		begin


		INSERT INTO CabAccess (UserID,iDatabase, iBucket, CabFilename, CabPath, StatusID, DateRequested)
			 VALUES (@userfound,@iDatabase, @iBucket, @szCabFilename, @szCabPath, 1, CURRENT_TIMESTAMP)
		end
	else
		begin
		
		UPDATE CabAccess SET StatusID=1, DateRequested=CURRENT_TIMESTAMP  WHERE (CabAccessID=@CabAccessID)


		end

	SELECT @CabAccessID= CabAccessID from CabAccess WHERE UserID = @userfound and iBucket=@iBucket and iDatabase=@iDatabase 
		and CabFilename=@szCabFilename and CabPath = @szCabPath

	if @CabAccessID  is NULL
		begin
		select @target = -1
		return @target
		end




	select @target = @CabAccessID
	RETURN @target

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE AddUserAndLevel
	@User varchar(50),
	@Domain varchar(50),
	@CurrTime varchar(50),
	@Level int
As
	set nocount on
	-- local var to hold record id
	declare @target int
	declare @levelDB int
	declare @userDB int
	declare @userID int

	-- do we already have this FileInCab?
	SELECT @userID = UserID FROM AuthorizedUsers WHERE 
		UserAlias =@User AND  UserDomain = @Domain

	if @userID is NULL
		begin	-- not there, create new record
		INSERT INTO AuthorizedUsers (UserAlias, UserDomain, DateSignedDCP)
			 VALUES (@User, @Domain,@CurrTime)

		SELECT @userID = UserID FROM AuthorizedUsers WHERE 
			UserAlias =@User AND  UserDomain = @Domain


		end
	else
		begin	-- update record with new signing date/time
			UPDATE AuthorizedUsers SET DateSignedDCP = @CurrTime 
				 WHERE (UserID = @userID)
		end

--	PRINT @userID

	-- we don't have the userid 
	if @userID is NULL
		begin
		select @userID = 0
		return @userID
		end
	
	select @levelDB = AccessLevelID FROM AccessLevels WHERE
		AccessLevelID = @Level

	-- bad level passed in
	if @levelDB is NULL
		begin
		select @levelDB = 0
		return @levelDB
		end

	SELECT @target = UserAccessLevelID FROM UserAccessLevels WHERE 
		UserID = @userID and AccessLevelID = @Level

	if @target is NULL
		begin	-- not there, create new record
		INSERT INTO UserAccessLevels (UserID, AccessLevelID)
			 VALUES (@userID, @Level)

		SELECT @target = UserAccessLevelID FROM UserAccessLevels WHERE 
			UserID = @userID and AccessLevelID = @Level

		end

	if @target is NULL
		begin
		select @target = 0
		return @target
		end

	-- return the UserAccessLevelID
	RETURN @target








GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE AddUserForApproval
	@ApprovalType int,
	@User varchar(50),
	@Domain varchar(50),
	@Reason varchar(250)

As
	set nocount on
	-- local var to hold record id
	declare @target int

	declare @userfound int
	declare @userApproved int


	SELECT @userfound = UserID FROM AuthorizedUsers WHERE UserAlias = @User and UserDomain = @Domain


	if @userfound is NULL
		begin
		select @target = -1
		return @target
		end

	SELECT @userApproved = UserID FROM Approvals WHERE UserID = @userfound and ApprovalTypeID=@ApprovalType

	if @userApproved is NULL
		begin	-- not there, create new record
		INSERT INTO Approvals (UserID,ApprovalTypeID, Reason, ApproverEmailStatus)
			 VALUES (@userfound,@ApprovalType,@Reason,0)

		SELECT @userApproved = UserID from Approvals WHERE UserID = @userfound

		if @userApproved is NULL
			begin
			select @target = -1
			return @target
			end

		end

	select @target = @userApproved
	RETURN @target

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE AddUserLevel 
	@UserID int,
	@Level int
As
	set nocount on
	-- local var to hold record id
	declare @target int
	declare @levelDB int
	declare @userDB int

	select @userDB = UserID from AuthorizedUsers WHERE
		UserID = @UserID

	if @userDB is NULL
		begin
		select @userDB = 0
		return @userDB
		end
	
	select @levelDB = AccessLevelID FROM AccessLevels WHERE
		AccessLevelID = @Level

	if @levelDB is NULL
		begin
		select @levelDB = 0
		return @levelDB
		end


	SELECT @target = UserAccessLevelID FROM UserAccessLevels WHERE 
		UserID = @UserID and AccessLevelID = @Level

	if @target is NULL
		begin	-- not there, create new record
		INSERT INTO UserAccessLevels (UserID, AccessLevelID)
			 VALUES (@UserID, @Level)

		SELECT @target = UserAccessLevelID FROM UserAccessLevels WHERE 
			UserID = @UserID and AccessLevelID = @Level

		end


	RETURN @target





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE ApproveUser
	@UserID int,
	@ApprovalTypeID int,
	
	@ApproverUserID int,

	@DateApproved varchar(50)

As
	set nocount on
	-- local var to hold record id
	declare @target int

	declare @userfound int
	declare @userApproved int


	SELECT @userApproved = UserID FROM Approvals WHERE UserID = @UserID and ApprovalTypeID = @ApprovalTypeID

	if @userApproved is not NULL
		begin	-- not there, create new record
			UPDATE Approvals SET ApproverUserID = @ApproverUserID,
				DateApproved = @DateApproved,RequesterEmailStatus=0
				 WHERE (UserID = @UserID and ApprovalTypeID = @ApprovalTypeID)

		end

	select @target = 0
	RETURN @target

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


-- return values:
-- 0 = user not in database
-- 1 = user in database or approved in approvals table
-- 2 = user access is denied
-- 3 = user in table, but not yet approved
-- 4 = user not in approvals table

CREATE PROCEDURE CheckApprovalAccess
	@User varchar(50),
	@Domain varchar(50),
	@Level int,
	@ApprovalTypeID int
As
	set nocount on
	-- local var to hold record id
	declare @target int
	declare @HasApproval int
	declare @DBUserID int
	declare @ApproverUserID int
	declare @Reason varchar(255)
	declare @DateApproved varchar(50)
	declare @ApprovalsUserID int




SELECT @DBUserID=AuthorizedUsers.UserID, @target=UserAccessLevels.AccessLevelID, 
   @HasApproval= Approvals.UserID
FROM UserAccessLevels INNER JOIN
    AuthorizedUsers ON 
    UserAccessLevels.UserID = AuthorizedUsers.UserID LEFT OUTER
     JOIN
    Approvals ON 
    AuthorizedUsers.UserID = Approvals.UserID
WHERE (AuthorizedUsers.UserAlias = @User) AND 
    (AuthorizedUsers.UserDomain = @Domain) AND 
    ((UserAccessLevels.AccessLevelID = 0) OR
    ((UserAccessLevels.AccessLevelID = @Level) AND 
    (AuthorizedUsers.DateSignedDCP IS NOT NULL) AND 
    (CURRENT_TIMESTAMP <= DATEADD(month, 12, 
    AuthorizedUsers.DateSignedDCP))))
ORDER BY UserAccessLevels.AccessLevelID DESC


	-- @target will point to the last value we read 

	if @target IS NULL
		begin
		select @target = 0
		return @target
		end
	else
		begin
		-- @target is 0 if access is denied, we return 2 in this case
		if  @target = 0 
			begin
			select @target = 2
			return @target
			end
		end


	SELECT @ApprovalsUserID = UserID,@ApproverUserID = ApproverUserID, @Reason = Reason, @DateApproved = DateApproved
	FROM Approvals
	WHERE UserID = @DBUserID AND ApprovalTypeID = @ApprovalTypeID

	-- not in this table, they need to be added
	if @ApprovalsUserID is NULL
		begin
--		print 'okay to pass'
		select @target = 4
		return @target
		end

	if @ApproverUserID is NULL or @Reason is NULL or @DateApproved is NULL
		begin
--		print 'not approved yet'
		select @target = 3
		return @target
		end

--	print 'approved'

	select @target = 1


	RETURN @target

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


-- -1 = no access to this cab
-- otherwise, statusid (which is >= 0)

CREATE PROCEDURE CheckRestrictedCabAccess
	@User varchar(50),
	@Domain varchar(50),
	@iDatabase int,
	@iBucket int,
	@szCabFilename varchar(100),
	@szCabPath   varchar(255)

As
	set nocount on
	-- local var to hold record id
	declare @target int

	declare @userfound int
	declare @userApproved int
	declare @CabAccessID int
	declare @StatusID int


	SELECT @userfound = UserID FROM AuthorizedUsers WHERE UserAlias = @User and UserDomain = @Domain


	if @userfound is NULL
		begin
		select @target = -1
		return @target
		end

	SELECT @userApproved = UserID FROM Approvals WHERE UserID = @userfound

	if @userApproved is NULL
		begin	
		select @target = -1
		return @target

		end

	SELECT @CabAccessID= CabAccessID, @StatusID=StatusID from CabAccess WHERE UserID = @userfound and iBucket=@iBucket and iDatabase=@iDatabase 
		and CabFilename=@szCabFilename and CabPath =@szCabPath

	if @CabAccessID  is NULL
		begin
		select @target = -1
		return @target
		end

	if @StatusID is NULL
		begin
		select @target = -1
		return @target
		end





	select @target = @StatusID
	RETURN @target

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE CheckUserAccess
	@User varchar(50),
	@Domain varchar(50),
	@Level int
As
	set nocount on
	-- local var to hold record id
	declare @target int



	SELECT @target= dbo.UserAccessLevels.AccessLevelID FROM 
		dbo.UserAccessLevels INNER JOIN dbo.AuthorizedUsers ON dbo.UserAccessLevels.UserID = dbo.AuthorizedUsers.UserID
		 WHERE (dbo.AuthorizedUsers.UserAlias = @User ) AND (dbo.AuthorizedUsers.UserDomain = @Domain) AND
		((AccessLevelID = 0) OR (AccessLevelID = @Level AND (AuthorizedUsers.DateSignedDCP IS Not Null AND
		CURRENT_TIMESTAMP <= DATEADD(month,12,DateSignedDCP) ) ) )
		ORDER BY AccessLevelID DESC

	-- @target will point to the last value we read 

	if @target IS NULL
		begin
		select @target = 0
		end
	else
		begin
		-- @target is 0 if access is denied, we return 2 in this case
		if  @target = 0 
			begin
			select @target = 2
			end
		else
			begin
			select @target = 1
			end
		end

--	PRINT @target

	RETURN @target

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


-- return values:
-- 0 = user not in database
-- 1 = user in database or approved in approvals table
-- 2 = user access is denied
-- 3 = user in table, but not yet approved
-- 4 = user not in approvals table


CREATE PROCEDURE CheckUserAccessApprovals
	@User varchar(50),
	@Domain varchar(50),
	@Level int,
	@ApprovalTypeID int
As
	set nocount on
	-- local var to hold record id
	declare @target int
	declare @HasApproval int
	declare @DBUserID int
	declare @ApproverUserID int
	declare @Reason varchar(255)
	declare @DateApproved varchar(50)
	declare @ApprovalsUserID int




SELECT @DBUserID=AuthorizedUsers.UserID, @target=UserAccessLevels.AccessLevelID, 
   @HasApproval= Approvals.UserID
FROM UserAccessLevels INNER JOIN
    AuthorizedUsers ON 
    UserAccessLevels.UserID = AuthorizedUsers.UserID LEFT OUTER
     JOIN
    Approvals ON 
    AuthorizedUsers.UserID = Approvals.UserID
WHERE (AuthorizedUsers.UserAlias = @User) AND 
    (AuthorizedUsers.UserDomain = @Domain) AND 
    ((UserAccessLevels.AccessLevelID = 0) OR
    ((UserAccessLevels.AccessLevelID = @Level) AND 
    (AuthorizedUsers.DateSignedDCP IS NOT NULL) AND 
    (CURRENT_TIMESTAMP <= DATEADD(month, 12, 
    AuthorizedUsers.DateSignedDCP))))
ORDER BY UserAccessLevels.AccessLevelID DESC


	-- @target will point to the last value we read 

	if @target IS NULL
		begin
		select @target = 0
		return @target
		end
	else
		begin
		-- @target is 0 if access is denied, we return 2 in this case
		if  @target = 0 
			begin
			select @target = 2
			return @target
			end
		end


	SELECT @ApprovalsUserID = UserID,@ApproverUserID = ApproverUserID, @Reason = Reason, @DateApproved = DateApproved
	FROM Approvals
	WHERE UserID = @DBUserID AND ApprovalTypeID = @ApprovalTypeID


	if @ApprovalsUserID is NULL
		begin
--		print 'okay to pass'
		-- not in this table, okay to pass if ApprovalTypeID=1
		if @ApprovalTypeID=1 
			begin
			select @target = 1
			return @target
			end
		else
		--  Otherwise, return 4
			begin
			select @target = 4
			return @target
			end

		end


	if @ApproverUserID is NULL or @Reason is NULL or @DateApproved is NULL
		begin
--		print 'not approved yet'
		select @target = 3
		return @target
		end

--	print 'approved'

	select @target = 1


	RETURN @target

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE dbo.DefragIndexes
         @maxfrag          decimal = 80.0     --Scan density < than this number
       , @ActualCountRatio int     = 10		 --The Actial count ratio > than this number
       , @ReportOnly       char(1) = 'Y'      --Change to 'Y' if you just want the report
AS

SET NOCOUNT ON
DECLARE @tablename      varchar (128)
      , @execstr        varchar (255)
      , @objectid       int
      , @LogicalFrag    decimal
      , @ScanDensity    decimal
      , @ratio          varchar (25)
      , @IndexName      varchar(64)
      , @StartTime      datetime
      , @EndTime        datetime

PRINT '/*****************************************************************************************************/'
PRINT 'Starting DEFRAG on ' + db_name()
      
SELECT @StartTime = getdate() 

PRINT 'Start Time: ' + convert(varchar(20),@StartTime) 

-- Declare cursor
DECLARE tables CURSOR FOR
   SELECT name FROM sysobjects 
   WHERE type = 'u'   --Tables only
   --and uid = 1        --dbo only
   ORDER BY name

-- Create the table
CREATE TABLE #fraglist (
           ObjectName     varchar(255)
         , ObjectId       int
         , IndexName      varchar(255)
         , IndexId        int
         , Lvl            int
         , CountPages     int
         , CountRows      int
         , MinRecSize     int
         , MaxRecSize     int
         , AvgRecSize     int
         , ForRecCount    int
         , Extents        int
         , ExtentSwitches int
         , AvgFreeBytes   int
         , AvgPageDensity int
         , ScanDensity    decimal
         , BestCount      int
         , ActualCount    int
         , LogicalFrag    decimal
         , ExtentFrag     decimal)

-- Open the cursor & Loop through all the tables in the database 
OPEN tables FETCH NEXT
   FROM tables
   INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') WITH FAST, TABLERESULTS, NO_INFOMSGS')
   FETCH NEXT 
      FROM tables INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

--Print SHOW_CONTIG Results if 'Y'
PRINT 'Fragmentation Before DBCC INDEXDEFRAG'
       SELECT left(ObjectName,35) 'TableName'
            ,left(IndexName,45)   'IndexName'
            ,LogicalFrag          'Logical Frag %'
            ,ScanDensity          'Scan Density %'
            , '[' + rtrim(convert(varchar(8),BestCount)) + ':' + rtrim(convert(varchar(8),ActualCount)) + ']' 'Ratio'
      FROM #fraglist    
      WHERE ScanDensity <= @maxfrag
      AND ActualCount  >= @ActualCountRatio
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0


-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
   SELECT ObjectName, 
          ObjectId,
          IndexName, 
          LogicalFrag, 
          ScanDensity, 
          '[' + rtrim(convert(varchar(8),BestCount)) + ':' + rtrim(convert(varchar(8),ActualCount)) + ']'
   FROM #fraglist
   WHERE ScanDensity <= @maxfrag
      AND ActualCount  >= @ActualCountRatio
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes FETCH NEXT FROM indexes INTO @tablename, @objectid, @IndexName, @LogicalFrag, @ScanDensity, @ratio

WHILE @@FETCH_STATUS = 0
BEGIN   

   PRINT '--Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',' 
       + RTRIM(@IndexName) + ') - Logical Frag Currently:'
       + RTRIM(CONVERT(varchar(15),@LogicalFrag)) + '%  - Scan Density Currently:' + @ratio
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ''' + RTRIM(@tablename) 
                      + ''', ''' + RTRIM(@IndexName) + ''')'

   If @ReportOnly = 'Y'
      BEGIN   
         EXEC (@execstr)   --Report and Execute the DBCC INDEXDEFRAG
      END
   ELSE
      BEGIN
         Print @execstr    --Report Only
      END

   FETCH NEXT FROM indexes INTO @tablename, @objectid, @IndexName, @LogicalFrag, @ScanDensity, @ratio END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes


SELECT @EndTime = getdate() 
PRINT 'End Time: ' + convert(varchar(20),@EndTime) 
PRINT 'Elapses Time: ' + convert(varchar(20), DATEDIFF(mi, @StartTime, @EndTime) ) + ' minutes.'

-- Delete the temporary table
DROP TABLE #fraglist






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE GetUserID
	@User varchar(50),
	@Domain varchar(50)

As
	set nocount on
	-- local var to hold record id
	declare @target int



	SELECT @target= UserID From AuthorizedUsers Where UserAlias = @User and UserDomain = @Domain


	-- @target will point to the last value we read 

	if @target IS NULL
		begin
		select @target = -1
		end

--	PRINT @target

	RETURN @target






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO





CREATE PROCEDURE OcaAddUserAndLevel
	@User varchar(50),
	@Domain varchar(50),
	@CurrTime varchar(50),
	@Level int
As	set nocount on
	-- local var to hold record id
	declare @target int
	declare @levelDB int
	declare @userDB int
	declare @userID int

	-- do we already have this FileInCab?
	SELECT @userID = UserID FROM OcaAuthorizedUsers WHERE 
		UserAlias =@User AND  UserDomain = @Domain

	if @userID is NULL
		begin	-- not there, create new record
		INSERT INTO OcaAuthorizedUsers (UserAlias, UserDomain, DateSignedDCP)
			 VALUES (@User, @Domain,@CurrTime)

		SELECT @userID = UserID FROM OcaAuthorizedUsers WHERE 
			UserAlias =@User AND  UserDomain = @Domain


		end
	else
		begin	-- update record with new signing date/time
			UPDATE OcaAuthorizedUsers SET DateSignedDCP = @CurrTime 
				 WHERE (UserID = @userID)
		end

--	PRINT @userID

	-- we don't have the userid 
	if @userID is NULL
		begin
		select @userID = 0
		return @userID
		end
	
	select @levelDB = AccessLevelID FROM OcaAccessLevels WHERE
		AccessLevelID = @Level

	-- bad level passed in
	if @levelDB is NULL
		begin
		select @levelDB = 0
		return @levelDB
		end

	SELECT @target = UserAccessLevelID FROM OcaUserAccessLevels WHERE 
		UserID = @userID and AccessLevelID = @Level

	if @target is NULL
		begin	-- not there, create new record
		INSERT INTO OcaUserAccessLevels (UserID, AccessLevelID)
			 VALUES (@userID, @Level)

		SELECT @target = UserAccessLevelID FROM OcaUserAccessLevels WHERE 
			UserID = @userID and AccessLevelID = @Level

		end

	if @target is NULL
		begin
		select @target = 0
		return @target
		end

	-- return the UserAccessLevelID
	RETURN @target



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO





CREATE PROCEDURE OcaAddUserForApproval
	@ApprovalType int,
	@User varchar(50),
	@Domain varchar(50),
	@Reason varchar(250)

As	set nocount on
	-- local var to hold record id
	declare @target int

	declare @userfound int
	declare @userApproved int


	SELECT @userfound = UserID FROM OcaAuthorizedUsers WHERE UserAlias = @User and UserDomain = @Domain


	if @userfound is NULL
		begin
		select @target = -1
		return @target
		end

	SELECT @userApproved = UserID FROM OcaApprovals WHERE UserID = @userfound

	if @userApproved is NULL
		begin	-- not there, create new record
		INSERT INTO OcaApprovals (UserID,ApprovalTypeID, Reason)
			 VALUES (@userfound,@ApprovalType,@Reason)

		SELECT @userApproved = UserID from OcaApprovals WHERE UserID = @userfound

		if @userApproved is NULL
			begin
			select @target = -1
			return @target
			end

		end

	select @target = @userApproved
	RETURN @target



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO





CREATE PROCEDURE OcaAddUserLevel 
	@UserID int,
	@Level int
As	set nocount on
	-- local var to hold record id
	declare @target int
	declare @levelDB int
	declare @userDB int

	select @userDB = UserID from OcaAuthorizedUsers WHERE
		UserID = @UserID

	if @userDB is NULL
		begin
		select @userDB = 0
		return @userDB
		end
	
	select @levelDB = AccessLevelID FROM OcaAccessLevels WHERE
		AccessLevelID = @Level

	if @levelDB is NULL
		begin
		select @levelDB = 0
		return @levelDB
		end


	SELECT @target = UserAccessLevelID FROM OcaUserAccessLevels WHERE 
		UserID = @UserID and AccessLevelID = @Level

	if @target is NULL
		begin	-- not there, create new record
		INSERT INTO OcaUserAccessLevels (UserID, AccessLevelID)
			 VALUES (@UserID, @Level)

		SELECT @target = UserAccessLevelID FROM OcaUserAccessLevels WHERE 
			UserID = @UserID and AccessLevelID = @Level

		end


	RETURN @target








GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE PROCEDURE OcaApproveUser
	@UserID int,
	@ApprovalTypeID int,
	
	@ApproverUserID int,

	@DateApproved varchar(50)

As	set nocount on
	-- local var to hold record id
	declare @target int

	declare @userfound int
	declare @userApproved int


	SELECT @userApproved = UserID FROM OcaApprovals WHERE UserID = @UserID and ApprovalTypeID = @ApprovalTypeID

	if @userApproved is not NULL
		begin	-- not there, create new record
			UPDATE OcaApprovals SET ApproverUserID = @ApproverUserID,
				DateApproved = @DateApproved
				 WHERE (UserID = @UserID and ApprovalTypeID = @ApprovalTypeID)

		end

	select @target = 0
	RETURN @target
























GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO





CREATE PROCEDURE OcaCheckUserAccess
	@User varchar(50),
	@Domain varchar(50),
	@Level int
As	set nocount on
	-- local var to hold record id
	declare @target int



	SELECT @target= dbo.OcaUserAccessLevels.AccessLevelID FROM 
		dbo.OcaUserAccessLevels INNER JOIN dbo.OcaAuthorizedUsers ON dbo.OcaUserAccessLevels.UserID = dbo.OcaAuthorizedUsers.UserID
		 WHERE (dbo.OcaAuthorizedUsers.UserAlias = @User ) AND (dbo.OcaAuthorizedUsers.UserDomain = @Domain) AND
		((AccessLevelID = 0) OR (AccessLevelID = @Level AND (OcaAuthorizedUsers.DateSignedDCP IS Not Null AND
		CURRENT_TIMESTAMP <= DATEADD(month,12,DateSignedDCP) ) ) )
		ORDER BY AccessLevelID DESC

	-- @target will point to the last value we read 

	if @target IS NULL
		begin
		select @target = 0
		end
	else
		begin
		-- @target is 0 if access is denied, we return 2 in this case
		if  @target = 0 
			begin
			select @target = 2
			end
		else
			begin
			select @target = 1
			end
		end

--	PRINT @target

	RETURN @target

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO





-- return values:
-- 0 = user not in database
-- 1 = user in database or approved in approvals table
-- 2 = user access is denied
-- 3 = user not yet approved

CREATE PROCEDURE OcaCheckUserAccessApprovals
	@User varchar(50),
	@Domain varchar(50),
	@Level int,
	@ApprovalTypeID int
As	set nocount on
	-- local var to hold record id
	declare @target int
	declare @HasApproval int
	declare @DBUserID int
	declare @ApproverUserID int
	declare @Reason varchar(255)
	declare @DateApproved varchar(50)
	declare @ApprovalsUserID int




SELECT @DBUserID=OcaAuthorizedUsers.UserID, @target=OcaUserAccessLevels.AccessLevelID, 
   @HasApproval= OcaApprovals.UserID
FROM OcaUserAccessLevels INNER JOIN
    OcaAuthorizedUsers ON 
    OcaUserAccessLevels.UserID = OcaAuthorizedUsers.UserID LEFT OUTER
     JOIN
    OcaApprovals ON 
    OcaAuthorizedUsers.UserID = OcaApprovals.UserID
WHERE (OcaAuthorizedUsers.UserAlias = @User) AND 
    (OcaAuthorizedUsers.UserDomain = @Domain) AND 
    ((OcaUserAccessLevels.AccessLevelID = 0) OR
    ((OcaUserAccessLevels.AccessLevelID = @Level) AND 
    (OcaAuthorizedUsers.DateSignedDCP IS NOT NULL) AND 
    (CURRENT_TIMESTAMP <= DATEADD(month, 12, 
    OcaAuthorizedUsers.DateSignedDCP))))
ORDER BY OcaUserAccessLevels.AccessLevelID DESC


	-- @target will point to the last value we read 

	if @target IS NULL
		begin
		select @target = 0
		return @target
		end
	else
		begin
		-- @target is 0 if access is denied, we return 2 in this case
		if  @target = 0 
			begin
			select @target = 2
			return @target
			end
		end


	SELECT @ApprovalsUserID = UserID,@ApproverUserID = ApproverUserID, @Reason = Reason, @DateApproved = DateApproved
	FROM OcaApprovals
	WHERE UserID = @DBUserID AND ApprovalTypeID = @ApprovalTypeID

	-- not in this table, okay to pass
	if @ApprovalsUserID is NULL
		begin
--		print 'okay to pass'
		select @target = 1
		return @target
		end

	if @ApproverUserID is NULL or @Reason is NULL or @DateApproved is NULL
		begin
--		print 'not approved yet'
		select @target = 3
		return @target
		end

--	print 'approved'

	select @target = 1


	RETURN @target

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE PROCEDURE OcaGetUserID
	@User varchar(50),
	@Domain varchar(50)

As	set nocount on
	-- local var to hold record id
	declare @target int



	SELECT @target= UserID From OcaAuthorizedUsers Where UserAlias = @User and UserDomain = @Domain


	-- @target will point to the last value we read 

	if @target IS NULL
		begin
		select @target = -1
		end

--	PRINT @target

	RETURN @target









GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO





CREATE PROCEDURE OcaRemoveUserLevel 
	@UserID int,
	@Level int
As	set nocount on
	-- local var to hold record id
	declare @target int
	declare @levelDB int
	declare @userDB int

	select @userDB = UserID from OcaAuthorizedUsers WHERE
		UserID = @UserID

	if @userDB is NULL
		begin
		select @userDB = 0
		return @userDB
		end
	
	select @levelDB = AccessLevelID FROM OcaAccessLevels WHERE
		AccessLevelID = @Level

	if @levelDB is NULL
		begin
		select @levelDB = 0
		return @levelDB
		end


	SELECT @target = UserAccessLevelID FROM OcaUserAccessLevels WHERE 
		UserID = @UserID and AccessLevelID = @Level

	if @target is not NULL
		begin	-- there, delete record
		DELETE FROM OcaUserAccessLevels WHERE UserID = @UserID and AccessLevelID = @Level

		end


	RETURN @target









GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE RemoveUserLevel 
	@UserID int,
	@Level int
As
	set nocount on
	-- local var to hold record id
	declare @target int
	declare @levelDB int
	declare @userDB int

	select @userDB = UserID from AuthorizedUsers WHERE
		UserID = @UserID

	if @userDB is NULL
		begin
		select @userDB = 0
		return @userDB
		end
	
	select @levelDB = AccessLevelID FROM AccessLevels WHERE
		AccessLevelID = @Level

	if @levelDB is NULL
		begin
		select @levelDB = 0
		return @levelDB
		end


	SELECT @target = UserAccessLevelID FROM UserAccessLevels WHERE 
		UserID = @UserID and AccessLevelID = @Level

	if @target is not NULL
		begin	-- there, delete record
		DELETE FROM UserAccessLevels WHERE UserID = @UserID and AccessLevelID = @Level

		end


	RETURN @target






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


-- return values:
-- 0 = user not in database
-- 1 = user in database or approved in approvals table
-- 2 = user access is denied
-- 3 = user in table, but not yet approved
-- 4 = user not in approvals table

CREATE PROCEDURE TestProc

As
	set nocount on
	-- local var to hold record id
	declare @target int
	
select @target = 52

print 'foo'  + CONVERT(char(19),@target)+'\n'
print @target
print '\n'

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE rolemember
	@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  MemberName = u.name,DbRole = g.name
			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  MemberName = u.name,DbRole = g.name
			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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE updatestats

@resample CHAR(8)='FULLSCAN'

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

 

            

 

            -- 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)='FULLSCAN') SET @exec_stmt = @exec_stmt + ' WITH fullscan'

                                    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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO