Voice of the DBA

Tag: T-SQL

  • Creating a DDL Trigger

    One of the most amazing features to an old SQL Server 4.2 guy was the addition of DDL triggers to the server. As with any trigger, these can be problematic in that they can overload a server, and they ALWAYS fire, so you can cause yourself problems, but in terms of auditing, I think they’re great.

    As a quick example, perhaps you’re worried about new logins, as I talk about in my AlwaysOn and Contained Databases in SQL Server 2012 presentation. You want to capture when a new login is created. You can do this with a DDL trigger like this one:

    USE master
GO
CREATE trigger CatchLogins on ALL Server
for CREATE_LOGIN
as
declare @data xml
set @data = eventdata()

SELECT @data

    That doesn’t do much, but if I run this code:

    CREATE LOGIN Delaney WITH PASSWORD = 'test'

    I get this result:

    ddl1

    Not overly helpful, but if you click on it, you see the event data as an XML document

    <EVENT_INSTANCE>
  <EventType>CREATE_LOGIN</EventType>
  <PostTime>2012-07-23T11:47:41.427</PostTime>
  <SPID>62</SPID>
  <ServerName>SEVENFALLS</ServerName>
  <LoginName>SevenFalls\Steve</LoginName>
  <ObjectName>Delaney</ObjectName>
  <ObjectType>LOGIN</ObjectType>
  <DefaultLanguage>us_english</DefaultLanguage>
  <DefaultDatabase>master</DefaultDatabase>
  <LoginType>SQL Login</LoginType>
  <SID>Djwpf8IHNUicam9m2DkoBQ==</SID>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>CREATE LOGIN Delaney WITH PASSWORD = '******'
</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

    I can parse this out and store it. What do I want? Probably I want the server and object, the date for tracking, maybe the creator, but definitely the SID. The text doesn’t help since it doesn’t have the password. All I can do then is go find the user or admin and ask them to recreate this login on the secondary servers.

    Let’s start parsing. You have two choices here with the XML: the .data or .query methods. There may be more, but that’s what I know. I’ll parse in two ways here:

    ALTER trigger CatchLogins on ALL Server
for CREATE_LOGIN
as
declare @data xml
set @data = eventdata()

select 
  @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
, @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(1000)')
, @data.query('(/EVENT_INSTANCE/ServerName)')

    This returns some data.

    ddl2

    You can see the .query returns XML, which (to me) is a hassle. So I’ll stick with the .value clause.

    I would probably create a table here that stores this data. If I used a generic table for multiple types of audit data, I’d need to include the type of event as well. You can just use the first XML document for different audit types to see what’s returned, and then deal with it as appropriate.

  • Contained Databases – Server Setting Matters

    In doing some additional testing on contained databases, I decided to create a new database on a new test VM.

    CREATE DATABASE cdb1
 containment = PARTIAL
;

    To my surprise, I got this error:

    Msg 12824, Level 16, State 1, Line 1

    The sp_configure value ‘contained database authentication’ must be set to 1 in order to create

    a contained database.  You may need to use RECONFIGURE to set the value_in_use.

    I checked the server setting, and sure enough the instance property was set to 0 (false).

    At first you might think this shouldn’t matter, but imagine you go to attach a backup of a contained database to an instance that doesn’t have this enabled. However there are a few security and administrative concerns over contained databases. We have the password policies, the potential collision of user names, and more.

    The easy fix is to enable the instance level setting. That’s easily done with this code:

    -- Set advanced options
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'contained database authentication', 1;
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO

    What about restoring a contained database backup? Surely it will just come online without the contained authentication?

    I tried it, before running the script above, and I got this error:

    containedfail

    Clearly the instance level setting matters. It’s easy to change, either in script or the GUI. However if you use the GUI, please don’t click OK and save the changes. Use the script button, save that for your logging/documentation, and then run the script.

  • Time Zones

    Visual Studio Lightswitch
    Designed to make developers’ lives easier. Shouldn’t we do that in T-SQL?

    I ran across this article on time zones, and was surprised that it was such a complex topic. It seemed to me that there should be an easy conversion function built into SQL Server that would do this:

    DECLARE @dt datetime
SELECT @dt = GETDATE()

SELECT CONVERT( DATETIMEOFFSET, @dt, 'CST')

    and I’d get my current time in Central Standard Time (CST).

    It’s 2012. We’ve learned that time zones are a part of applications. SQL Server is built for work with web applications, which usually span time zones. Developers can code around this, but don’t we want to give them tools to save time? Isn’t that the whole idea behind Lightswitch, ORMs, and most of the new languages in the world? To make developers more productive?

    CONVERT still has crazy numeric codes for conversion. Wouldn’t it make more sense to do this:

    DECLARE @dt datetime
SELECT @dt = GETDATE()

SELECT CONVERT( datetime, @dt, 'dd.mm.yy')

    than this?

    DECLARE @dt datetime
SELECT @dt = GETDATE()

SELECT CONVERT( datetime, @dt, 104)

    Time zone abbreviations are well known. Shouldn’t they be built into SQL Server?

    Sometimes I wonder if we are looking too much into things we can add to an application rather than the things that we can improve. Especially those things we use constantly.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Time Zones

    Why don’t we have a function like the one in this article to convert from time zone to time zone? Seems like something SQL Server should have added awhile ago.