Voice of the DBA

Tag: syndicated

Forty Five Minutes

Excellent: Dilbert on data analysts

The best part is the last quote: I like how you punctuate your ignorance with certainty.

August 1, 2012
Password Resets

I read this piece from Troy Hunt, which is a long look at the password reset process for a web application. It’s one of the first that I’ve seen which talks about the different implementations, along with the pitfalls and advantages of each.

It’s a great look at passwords, and there are definitely a few things in there I think should be built into authentication frameworks. I know we need to change a few things at SQLServerCentral and I’ve added them to the list.

Pass this one along to your developers. They should be aware of this stuff.

July 31, 2012
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:

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.

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.
July 31, 2012
#GetHawt – The Summer 2012 Edition

Jen McCown started a #GetHawt set of posts last year and I think it went well. It must have, so she started an invitation for a summer competition. Silly me hadn’t been paying much attention, even though I’d seen the tweets. I finally caught up ~~this~~ last week, and decided to unofficially join in.

Update, I meant to publish this on Friday, but didn’t get it completed in time, so I’m sending it out today.

I’ve been running for some time. I keep a log (if you care), and today is day 1427 for me of running at least a mile every day. On the US Running Streak Association site, I’m #228 on the active list.

In all the time, however, I haven’t gotten in great shape. I’m fit, and I feel wonderful, but I haven’t been what you’d call Hawt-shape. Years ago I did the Body for Life diet/challenge with my wife and we got in great shape together. She wanted to try it again, and we started it back up a little over 3 weeks ago. I weighed 224 late last year, which was a bit heavy. I’d like to get lower, but I’m not overly driven by weight.

I weighed myself this week on my scale and it said 205. Note that 205lbs on my scale does not necessarily bear any relation to 205 on any other scale in the universe. We’ve had this old, analog scale since 1992 or so and I can only use relative measurements. I know I was north of 220 earlier this year, so I have slimmed a bit.

It’s going well, and I like it. In terms of the #GetHawt competition, it lines up OK. I drink a ton of water, exercise every day, eat 6 meals a day, get one day off each week to do anything. In terms of the points for last week for me:

Friday – Good day exercising with my son and running for 20. Good meals, with grilled, low fat foods for 30 points, lots of water, lots of sleep (I love summer), sticking with the habits of the new routine, but had a soda. 85 points

Saturday – Free day for me. Ate decent, but enjoyed myself with pizza, tortilla chips and a brownie desert. 20 points for running 3.5 miles, 6 points for one decent meal (it’s free day), 10 pts for water, 15 pts for sleep, for a total of 51.

Sunday – baseball and a 2mi run, water, good meals (6 small meals, no snacks), sleep, got back into the habit of chores. 95 pts since I have no teammates.

Monday – lift, run, water, eating all good. Talked my wife into lifting with my son and I, slept. Sticking to the good habits of the diet, and no soda (trying to cut down), so 100 pts.

Tuesday – Great run, more of the same, but no communication today since my wife was gone. 95 points.

Wednesday – Run, bike, etc. 20 points. Ate well, even threw some candy in the trash without eating it 30 points. water, sleep, 25pts. Communication with my wife and kid 5 pts. Special trip to get a diet coke, -10 points. 70pts.

Thursday – Not a bad day, but I missed the lifting today. I got busy, and ran but my exercise is a getting a bit off track. 20 points. I’ll call it only 18 points for food since the kids got me to stop after volleyball and have part of a burrito. 10 for water, and -10 for a large coke zero. My wife avoided me and I didn’t chase her down. 38 points.

Friday? Up early to fly to Sacramento for SQL Saturday #144. I’ll mostly stuck to the diet. I didn’t have any alcohol at the party, but couldn’t resist Will Meier’s pulled pork. I did mostly have vegetables, and had a good eating day. Call it 20 points for a run in the park, 24 points eating, 10 with lots of water, and 10 for avoiding diet drinks and beer. 64 points.

In a week, that’s 534 points. Not sure if that’s good or bad, but I managed that on Fri-Thur. I wasn’t a strict dieter on the Body for Life diet on Sat/Sun, but I was pretty good.

Even with a double double from In-N-Out on Saturday on the way to the airport.

July 30, 2012