Rename SQL installation

Am taking no credit for this.
Script from a colleague, it lets you rename a sql server, i.e. tell SQL about the fact that the server has been renamed!

DECLARE  @machine  SYSNAME,
        @instance SYSNAME

SELECT @instance = CASE
                  WHEN CHARINDEX('\',@@SERVERNAME) = 0 THEN ''
                  ELSE SUBSTRING(@@SERVERNAME,CHARINDEX('\',@@SERVERNAME),
                                   (LEN(@@SERVERNAME) + 1) - CHARINDEX('\',@@SERVERNAME))
                  END

SELECT @machine = CONVERT(NVARCHAR(100),SERVERPROPERTY('MACHINENAME')) + @instance;


EXEC SP_DROPSERVER
 @@SERVERNAME;

EXEC SP_ADDSERVER
 @machine ,'local'

NB : Remember to restart the SQL Server service after running the script.

TSQL 2005 - ALL

SQL Function to evaluate results of a subquery >

IF 130 > ALL (SELECT Rate FROM HumanResources.EmployeePayHistory)
 PRINT 'No employee is paid more than 130.'
ELSE
 PRINT 'There are employees paid more than 130.'

TSQL : Users & Logins

TSQL for querying systems objects for database users & logins (2000 & 2005) -

--sql 2000 -- list database users
select name 
from sysusers
where islogin = 1
and uid not in (0,1,2,3,4) -- exclude internal sql acounts

--sql 2005 -- list database users
select name 
from sys.database_principals
where type = 'S' -- sql login
and principal_id not in (0,1,2,3,4) -- exclude internal sql acounts

-- sql 2000 -- list orphanned users for current database
select name 
from sysusers
where islogin = 1
and uid not in (0,1,2,3,4) 
and sid not in (select sid from sys.syslogins) -- exclude mapped logins

--sql 2005 -- list orphanned users for current database
select name 
from sys.database_principals
where type = 'S' -- sql login
and principal_id not in (0,1,2,3,4) -- exclude internal sql acounts
and sid not in (select sid from sys.server_principals) -- exclude mapped logins

-- code to demonstrate sql users present in db, but not mapping to sql server logins

select * from sys.sysusers
      join sys.syslogins
  on sys.sysusers.name = sys.syslogins.name
    and sys.sysusers.sid <> sys.syslogins.sid

--sql 2000 - users that need remapping to login following RESTORE
select 'sp_change_users_login ''update_one'',''' + name + ''','''+ name + '''' 
from sysusers where islogin = 1
and uid not in (0,1,2,3,4) 
and sid not in (select sid from sys.syslogins) -- exclude mapped logins

--sql 2005 - users that need remapping to login following RESTORE
select 'sp_change_users_login ''update_one'',''' + name + ''','''+ name + '''' 
from sys.database_principals
where type = 'S' -- sql login
and principal_id not in (0,1,2,3,4) -- exclude internal sql acounts
and sid not in (select sid from sys.server_principals) -- exclude mapped logins

TSQL : Blocking & Locking

Tasks waiting right now -

SELECT * FROM Sys.dm_os_waiting_tasks

Tasks being blocked right now -

SELECT * FROM Sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL

Bit difficult to test when your system is running fine, but a query for clearly seeing the details of blocking issues -

SELECT PROCESS_BLOCKING.SPID                 'Holding ID',
RTRIM(PROCESS_BLOCKING.STATUS)        'Status',
'Lock Type' = CASE SYSLOCKINFO.RSC_TYPE
WHEN 1 THEN NULL
WHEN 2 THEN 'DATABASE'
WHEN 3 THEN 'FILE'
WHEN 4 THEN 'INDEX'
WHEN 5 THEN 'TABLE'
WHEN 6 THEN 'PAGE'
WHEN 7 THEN 'KEY'
WHEN 8 THEN 'EXTENT'
WHEN 9 THEN 'RID'
WHEN 10 THEN 'APPLICATION'
ELSE NULL
END,
SUSER_SNAME(PROCESS_BLOCKING.SID)     'Holding User',
SUSER_SNAME(PROCESS_WAITING.SID)     'Waiting User',
PROCESS_WAITING.SPID                 'Waiting ID',
'Database' = CASE
WHEN SYSLOCKINFO.RSC_DBID = 0 THEN '[NULL]'
ELSE DB_NAME(SYSLOCKINFO.RSC_DBID)
END,
SYSLOCKINFO.RSC_OBJID            'Object ID',
RTRIM(PROCESS_BLOCKING.HOSTNAME)      'Holding Host',
RTRIM(PROCESS_WAITING.HOSTNAME)      'Waiting Host',
RTRIM(PROCESS_BLOCKING.PROGRAM_NAME)  'Holding Program',
RTRIM(PROCESS_WAITING.PROGRAM_NAME)  'Waiting Program',
PROCESS_BLOCKING.CMD                  'Holding Command',
PROCESS_WAITING.CMD                  'Waiting Command',
PROCESS_BLOCKING.CPU                  'CPU Time',
PROCESS_BLOCKING.PHYSICAL_IO          'I/O',
PROCESS_BLOCKING.MEMUSAGE             'Mem Usage'
FROM   MASTER.DBO.SYSLOCKINFO
JOIN MASTER.DBO.SYSPROCESSES PROCESS_BLOCKING
ON SYSLOCKINFO.REQ_SPID = PROCESS_BLOCKING.SPID
JOIN MASTER.DBO.SYSPROCESSES PROCESS_WAITING
ON SYSLOCKINFO.REQ_SPID = PROCESS_WAITING.BLOCKED
AND PROCESS_BLOCKING.SPID = PROCESS_WAITING.BLOCKED
WHERE  PROCESS_BLOCKING.SPID <> @@SPID

Find all internal sql objects (including undocumented ones)

Note is_ms_shipped clause below -

SELECT * FROM sys.all_objects
WHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC')
AND [is_ms_shipped] = 1
ORDER BY [name];

To find functions, use type of  -
FN SQL Scalar Function
IF Inline Table Valued Function
TF SQL Table Valued Function

To find views, use type of  -
V View

To find procedures, use type of -
P SQL Stored Procedure
PC CLR Stored Procedure
X Extended Stored Procedure

ASP : Capturing SQL Injection attempts

if instr(Request.ServerVariables("QUERY_STRING"),"'") <> 0 THEN response.redirect "injectionattempt.asp?" & Request.ServerVariables("QUERY_STRING")
if instr(Request.ServerVariables("QUERY_STRING"),";") <> 0 THEN response.redirect "injectionattempt.asp?" & Request.ServerVariables("QUERY_STRING")
if instr(Request.ServerVariables("QUERY_STRING"),",") <> 0 THEN response.redirect "injectionattempt.asp?" & Request.ServerVariables("QUERY_STRING")

I use these lines at the top of pages that pass variables via the query string (the url itself). They search for ' , and ; which are characters which could break a sql query and enable someone to add a command into the sql being executed.

By calling injectionattempt.asp in this way I can capture the event and send myself an email letting me know this has occurred.

Determine SQL Authentication Method (2005)

Query to determine authentication method in use between client & server -

SELECT AUTH_SCHEME
FROM   SYS.DM_EXEC_CONNECTIONS
WHERE  SESSION_ID = @@SPID;

Possible return values are -

Login Type	Authentication Scheme
SQL	SQL
Windows
NTLM KERBEROS DIGEST BASIC NEGOTIATE