SQL 2008 : High Availability Whitepaper

Just out, a whitepaper on achieving High Availability in SQL 2008

http://msdn.microsoft.com/en-us/library/ee523927.aspx


Looking at the feature table, i'm glad I'm running Enterprise edition!

TSQL : Comma Separated List of Columns

Like the title says, how to generate a comma separated list of columns for a given table...

DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName  VARCHAR(100)
DECLARE @CommaSeparatedColumnList VARCHAR(MAX)

SET @SchemaName = 'myschema'
SET @TableName  = 'mytable'
SET @CommaSeparatedColumnList = ''

SELECT @CommaSeparatedColumnList = COALESCE(@CommaSeparatedColumnList + '[' + COLUMN_NAME + '],','')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaName
AND TABLE_NAME = @TableName          
ORDER BY ORDINAL_POSITION
SET @CommaSeparatedColumnList = LEFT(@CommaSeparatedColumnList,LEN(@CommaSeparatedColumnList)-1)

SELECT @CommaSeparatedColumnList

2 ways to audit all logins

1) Via Server Properties -


2) via a TSQL script -

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3
GO

MCITP : Database Administrator 2008

Following passing my MCTS on Monday , I passed MCITP Database Administrator 2008 this morning.

Contemplating the development track (70-433 & 70-451) next month to bring me up to date.

r

Bookmark : Management

A colleague found this today. An excellent post nicely summing up managing technical teams...

Opinion : The unspoken truth about managing geeks

MCTS : Database Administration 2008

Just a quick note to say I passed 70-432 : Microsoft SQL Server 2008, Implementation and Maintenance yesterday.

I used the MCTS Self-Paced Training Kit (Exam 70-432) if you want to do the same.

r

Stored Procedures : Execute as owner

Using EXECUTE AS OWNER in a stored procedure definition allows you to raise permissions for the execution of the procedure.

This enables a application login with low privileges to perform owner (dbo) privileged functionality using just the execute permissions on the sproc.

CREATE PROCEDURE dbo.EmptyMyTable

WITH EXECUTE AS OWNER

AS

BEGIN

TRUNCATE dbo.TableA

END

Clay Lenhart : SQL Server Security with EXECUTE AS OWNER