More fun with the February 2009 SQL Security Patch

On one of our clustered instances, we had to leave the services running for the February 2009 SQL Security Patch install to initiate successfully. We did not investigate the root cause since it was in the middle of a very busy period of work & we unfortunately didn't have the time. After applying the patch, we started getting complaints from users that their application was unable to login to the DB. Looking at the SQL error logs we saw the following errors...

Login failed for user 'Fake_User'. The user is not associated with a trusted SQL Server connection. [CLIENT: 127.0.0.1]
Error: 18452, Severity: 14, State: 1.

The login in question is a SQL login not Windows so this made no sense to us, on parsing the SQL error logs we noticed this (the instance had been running in MIXED mode)...

Authentication mode is WINDOWS-ONLY.

We changed the instance back to MIXED (SQL Server and Windows Authentication) mode & that resolved the issue. Thought I'd share this since it made for an interesting few minutes.

Searching for a peice of text across a DB

Awhile back I had the need to search for a specific peice of text across a database. After playing with the code I came up with the below stored proc. I placed the SP in our "Scripting/Administation" DB. It takes a param of database & the value to find, you can use wildcards in the value to find. The SP returns the table, column & number of occurences of the searched value.

USE DBAScriptDB
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROC dbo.usp_SearchTextAcrossDB
    @DBToSearch VARCHAR(512),
    @ValToFind VARCHAR(512)
AS
/*    --------------------------------------------------------
        Author  :    
        Date    : 
        Purpose : Search across all Tables/Columns in any DB for a text value. 
                            Wildcards may be used in @ValToFind.
        DB(s)        : DBAScriptDB, all DBs
        Modification History
        Date        Programmer         Description
        --------    -------------     ----------------   
   
*/    ---------------------------------------------------------
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
    SET NOCOUNT ON
    
    DECLARE @errTxt VARCHAR(256),
                    @objName VARCHAR(192),
                    @own VARCHAR(384), 
                    @tbl VARCHAR(384), 
                    @col VARCHAR(384),
                    @sqlSrch VARCHAR(8000),
                    @sqlFld VARCHAR(8000)
    
    CREATE TABLE #Results
        (
            Table_Name VARCHAR(384),
            Column_Name VARCHAR(384),
            Occurrence_Count INT      
        )

    CREATE TABLE #TblFlds
        (
            TblOwner VARCHAR(384),
            TblName VARCHAR(384),
            ColName VARCHAR(384)
        )

    SELECT    @objName = 'dbo.usp_SearchTextAcrossDB'

    SELECT @sqlFld = 
        'INSERT INTO #TblFlds (TblOwner, TblName, ColName)' + CHAR(10) +
        '    SELECT u.name, o.name, ''['' + c.name + '']''' + CHAR(10) +
        '    FROM ' + @DBToSearch + '.dbo.syscolumns c (NOLOCK)' + CHAR(10) +
        '        INNER JOIN ' + @DBToSearch + '.dbo.sysobjects o (NOLOCK) ON c.id = o.id' + CHAR(10) +
        '        INNER JOIN ' + @DBToSearch + '.dbo.sysusers u (NOLOCK) ON o.uid = u.uid' + CHAR(10) +
        '        INNER JOIN ' + @DBToSearch + '.dbo.systypes t (NOLOCK) ON c.xusertype = t.xusertype' + CHAR(10) +
        '    WHERE o.type = ''U''' + CHAR(10) +
        '                AND (t.name = ''varchar'' OR t.name = ''char'' OR t.name = ''nvarchar'' OR t.name = ''nchar'' OR t.name = ''text'' OR t.name = ''ntext'' OR t.name = ''sysname'')' + CHAR(10) +
        '    ORDER BY 1, 2'

    EXEC (@sqlFld)
    
    WHILE EXISTS (SELECT TblOwner FROM #TblFlds)
        BEGIN
            SELECT @own = (SELECT TOP 1 TblOwner FROM #TblFlds)
            
            SELECT @tbl = (SELECT TOP 1 TblName FROM #TblFlds WHERE TblOwner = @own)
            
            SELECT @col = (SELECT TOP 1 ColName FROM #TblFlds WHERE TblOwner = @own AND TblName = @tbl)
            
            DELETE #TblFlds 
            WHERE TblOwner = @own
                AND TblName = @tbl 
                AND ColName = @col
        
            IF @col IS NOT NULL
                BEGIN
                    SET @sqlSrch = 'SELECT ''' + @own + '.' + @tbl + ''', ''' + @col + ''', COUNT(*) FROM ' + @DBToSearch + '.' + @own + '.' + @tbl + ' (NOLOCK) WHERE ' + @col + ' LIKE ''' + @ValToFind + ''''
                    
                    INSERT INTO #Results (Table_Name, Column_Name, Occurrence_Count)
                        EXEC (@sqlSrch)
                END
            
            IF (SELECT COUNT(TblOwner) FROM #TblFlds) <= 0
                BREAK
        END

    IF @@ERROR <> 0
        BEGIN
            SET @errTxt = 'Exception: ' + @objName + ' occured in SELECT Statement'
            RAISERROR(@errTxt, 1, 2)
        END

    SELECT Table_Name, Column_Name, Occurrence_Count 
    FROM #Results 
    WHERE Occurrence_Count > 0 
    ORDER BY 1

    DROP TABLE #TblFlds, #Results
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO