Limit SQL Server memory

Set memory available to a sql instance
In this example we do so to 500MB (this was for testing instances on local pc)

sp_configure 'show advanced options',1
go
reconfigure
go

sp_configure 'max server memory', 500
go
reconfigure
go

Fetching local copy of Embedded Flash Movies

Saving Website Files in Firefox

[ 1 ] Tools > Page Info
[ 2 ] Media tab.
{ list of downloaded files for that page are shown }
[ 3 ] Locate desired .SWF file and click 'save as'

Get source movie

[ 1 ] Open SWF file in notepad. (Right-Click, Open With - Chose Notepad)
[ 2 ] Look in markup for name of file. (in this case .FLV file)
[ 3 ] Download the .FLV file directly from the original site.

TSQL Function : fn_SqueezeSpaces

This function reduces Multiple Spaces to a single instance.
Good for cleaning up text >

CREATE FUNCTION [dbo].[fn_SqueezeSpaces] (@String varchar(8000)) 
RETURNS VARCHAR(8000) 
AS 
BEGIN 
RETURN REPLACE(REPLACE(REPLACE(@String,' ',' '),' ',''),'','')
END

Disabling / Disable an index (sql 2005+)

'Why?' was my first thought, but then I remembered that when loading data to a table, it is faster without indexes in place

-- disable an index >
ALTER INDEX IX_Contact_EmailAddress ON Person.Contact DISABLE

-- re-enable the index (by rebuilding) >
ALTER INDEX IX_Contact_EmailAddress ON Person.Contact REBUILD

-- see what indexes are disabled >
SELECT [NAME], [TYPE_DESC], [IS_DISABLED] FROM SYS.INDEXES WHERE [IS_DISABLED] = 1

Kill Processes Cursor

Kill all processes running by a user >

DECLARE KILLUSERPROCESS CURSOR FORWARD_ONLY FOR
SELECT SPID
FROM   SYSPROCESSES
WHERE  LTRIM(RTRIM(LOGINAME)) = 'login'

DECLARE  @intSPID INT
DECLARE  @chvDSQL VARCHAR(128)

OPEN KILLUSERPROCESS
FETCH NEXT FROM KILLUSERPROCESS INTO @intSPID

WHILE (@@FETCH_STATUS = 0)
  BEGIN
    IF EXISTS (SELECT SPID
               FROM   SYSPROCESSES
               WHERE  LTRIM(RTRIM(LOGINAME)) = 'login'
                      AND SPID = @intSPID)
      BEGIN
        SET @chvDSQL = 'KILL ' + CAST(@intSPID AS VARCHAR)        
        EXEC( @chvDSQL)
      END
    
    FETCH NEXT FROM KILLUSERPROCESS INTO @intSPID
  END

CLOSE KILLUSERPROCESS
DEALLOCATE KILLUSERPROCESS

Generic DML Trigger Code

Generic code to cope with DML actions on your tables

CREATE TRIGGER [schemaname].[TRG_triggername]
ON [schemaname].[tablename]
FOR DELETE,INSERT,UPDATE
AS
DECLARE @ACTION CHAR(6)
DECLARE @DELETE BIT
DECLARE @INSERT BIT

SET @DELETE = 0
SET @INSERT = 0

IF EXISTS (SELECT TOP 1 1 FROM DELETED)  SET @DELETE = 1
IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @INSERT = 1

IF @INSERT = 1 AND @DELETE = 1 SET @ACTION = 'UPDATE'
IF @INSERT = 1 AND @DELETE = 0 SET @ACTION = 'INSERT'
IF @DELETE = 1 AND @INSERT = 0 SET @ACTION = 'DELETE'
IF @INSERT = 0 AND @DELETE = 0 RETURN

/*

Do Stuff here.
Refer to Added and Updated records from the INSERTED table
 > select * from inserted
Refer to Removed records from the DELETED table
 > select * from deleted
e.g.
 > INSERT INTO AuditTable 'INSERT', GETDATE(), * FROM INSERTED

*/
GO

Service Pack / Hotfix on SQL clusters

Guidelines for a smooth install of a Service Pack or Hotfix on a clustered SQL environment >

1 Install SP on first node
2 Failover active cluster groups to second node (Cluster Administrator)
3 Restart first node

(await restart of first node)

4 Install SP on second node
5 Failover active cluster groups to first node
6 Restart second node

(await restart of second node)

7 Test failing over of each group, leaving them back on original nodes.

SQL 101 : Security Links

SQL 2005

Microsoft Whitepaper - SQL Server 2005 Security Best Practices - Operational and Administrative Tasks
http://www.microsoft.com/technet/prodtechnol/sql/2005/sql2005secbestpract.mspx

SQL Server 2005 Best Practices Analyzer
http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en

Technet - SQL Sever 2005 - Security & Protection
http://technet.microsoft.com/en-gb/sqlserver/bb331769.aspx
Publish Post
SQL 2000

SQL Server 2000 Security Features & Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec01.mspx


General Security

Microsoft Security Bulletin
http://www.microsoft.com/technet/security/Bulletin/advance.mspx

sqlsecurity.com
http://www.sqlsecurity.com/

insecure.org
http://insecure.org/

Networking : Kerberos Authentication in SQL

Security Account Delegation
http://msdn.microsoft.com/en-us/library/aa905162(SQL.80).aspx


Understanding Kerberos and NTLM Authentication
http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

Kerberos Authentication with Sql Server
http://technet.microsoft.com/en-us/library/cc280745.aspx

Update 12/08/2009 -

Using Kerberos Authentication with Sql Server 2008
http://msdn.microsoft.com/en-us/library/ee191523.aspx

SQL : Data Search Script - Version 2

sql data search : version 2
searches an entire database for a search string

1) displays progress of search in the messages window,
2) takes search term as a parameter
3) provides sql at the end to examine the located data

/*
version 2 :    1) displays progress of search in the messages window,
               2) takes search term as a parameter
               3) provides sql at the end to examine the located data
*/
declare @columncount int
declare @searchdata varchar(255)
-- set string to search for here
set @searchdata = 'Utility%Connections'

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

SQL 2005 : Enabling XP_cmdshell

This is considered a big security 'no no', with access to external functionality preferred by developing a CLR assembly. With cmdshell enabled, users can effectively run any command :(

EXEC sp_configure 'show advanced option', '1'
RECONFIGURE;
GO

EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE;

-- remember to turn advanced options off again!
EXEC sp_configure 'show advanced option', '0'
RECONFIGURE
GO

SQL 2005 : Enabling common language runtime (clr)

During a recent install I had to do this via tsql as the Surface Area Configuration tool failed to do so!

EXEC SP_CONFIGURE 'show advanced options' , '1';
GO
RECONFIGURE; 
GO

EXEC SP_CONFIGURE 'clr enabled' , '1' 
GO
RECONFIGURE; 
GO

-- remember to turn advanced options off again!
EXEC sp_configure 'show advanced option', '0' 
RECONFIGURE
GO