Ready, Aim, Fire that Trigger!

As a recap, last week I started an experiment with my blog. I decided to post about using a script to routinely check database servers.  If you’re interested in the first post in this series, check out “Is It Me or Is That an Error in Your Log?”.  Since I haven’t heard anything good or bad from anyone, I shall continue.  This week I’m writing about system triggers and why you might want to think about checking for them. In addition, how to check for new or missing linked servers. Note: If I got something wrong, please let me know a.s.a.p.! I’m by no means an expert.

Continuing on… For those who aren’t sure what a trigger is, it is a database object that is basically a stored procedure but it is attached to a table. There are DDL (Data Definition Language) and DML (Data Manipulation Language) triggers. DDL triggers are new starting with SQL Server 2005.

A DML trigger is fired (i.e. executed) either before or after an insert, update, or delete statement is executed. Basically, it comes down to executing code based on a data manipulation statement. A very common use of a DML trigger is for auditing. Let’s say you have a very important table and you want to keep track of who is adding, changing, or removing the records. You can create DML triggers for that table to insert records into an audit log table indicating a record was added, modified or removed along with which fields, who did it, and when. Aha! It was Colonel Mustard in the Repository table with the Pipe-Delimited comment!

A DDL trigger fires based on a data definition statement being executed, such as create table, drop table, alter login, and so on. They can be specified for pretty much any DDL event that starts with create, alter, or drop. They can also be created on a database level or on a server level which is kinda cool and kinda scary at the same time.

The cool part is you can fire off code whenever someone attempts to do something like create a new database. For example, you can have it log information indicating user Squirrel attempted to create a new database called Nuts. You can have it print a message telling Squirrel it does not have permission to create Nuts, log the appropriate information to a table, and so on.

Now here’s the potentially scary part. With both types of triggers, they execute in the context of the user executing it. Huh? Meaning, if you have sysadmin permissions and you execute a DDL statement such as alter table which fires a trigger, the code in the trigger runs under your permission level.

Let’s say you inherited a database called Nuts. Unbeknownst to you, someone had created a DDL trigger on the database that executes whenever someone creates, alters, or drops a table. The code in the trigger grants database owner permissions to user “Squirrel” or worse, grants sysadmin permissions to that user.  So if someone with sysadmin privileges, for example, alters table,   the grant statement executes and now Squirrel has access to your server.

So how do you protect your servers? Luckily, you have some options.

1. Disable server and/or database level triggers.

DISABLE TRIGGER ALL ON DATABASE
DISABLE TRIGGER ALL ON ALL SERVER

2. Monitor / check for server and database level triggers.

Below is the code I use. Selecting from sys.triggers returns all DML and DDL triggers on the current database. Selecting from sys.server_triggers returns all DDL triggers at the server instance.

declare @chvServer varchar(50), @chvVersion varchar(20)
select @chvServer = convert(varchar, SERVERPROPERTY(‘ServerName’))
select @chvVersion = convert(varchar, SERVERPROPERTY(‘ProductVersion‘))
select ‘Server’=@chvServer , ‘Version’=@chvVersion

if left(@chvVersion,1) = 9 or left(@chvVersion,2) = 10
begin
      select ‘check for system-level triggers…’
      select [type], [name], parent_class_desc
      from sys.triggers
      union
      select [type], [name], parent_class_desc
      from sys.server_triggers
end

There’s a lot of great information out there on trigger security. One of them is “Managing Trigger Security” on TechNet.

And lastly for this week, linked server checks. To keep it simple, you can just do a select on the sys.servers catalog view (SQL 2005 & 2008) or sysservers (SQL 2000). The record for the server you’re running the query on is indicated by server_id = 0. Anything greater than 0 indicates a linked server. It may be a good idea to keep track of linked servers in case someone adds one or one goes missing. You can read more on the details via Books On Line (BOL).

select ‘Check servers…
select server_id
,[name]
,product
,provider
,data_source
,location,provider_string
,[catalog]
,connect_timeout
,query_timeout
,is_linked
,is_remote_login_enabled
,is_rpc_out_enabled
,is_data_access_enabled
,is_collation_compatible
,uses_remote_collation
,collation_name
,lazy_schema_validation
,is_system
,is_publisher
,is_subscriber
,is_distributor
,is_nonsql_subscriber
,modify_date
from sys.servers

Depending on feedback (good, bad, or ugly), next week I’ll discuss failures. As in backup jobs and database mail. Updated: For the next post in this series, see “Failure? What Failure?”.

So… what happened at work this week, you may be wondering? Well, my DBA buddy has left us for two whole work days (five days total). I think she also left a science experiment in her coffee cup. On Wednesday, I swear I saw it move closer towards me. By Thursday, I thought I heard it singing “Your code is like bad coffee / bad coffee is what I need / whoa oh oh / make it up just like bad coffee / there ain’t no dba that can cure my unease”… needless to say, it felt like a long week *no! really?*

I got a bit of a chuckle when I was informed that there is a table in a database with a column called “thing”. I kid you not! Umm… yeah… could they be any more vague? Know what’s even better?  It’s a vendor app! *sigh*  At least it keeps the code interesting. lol 🙂

Let’s see… mind control experiment continuation. Wore a green shirt to work one day. So did our boss… and the server group manager… and our adopted developer… hmm… thought about calling up my DBA buddy to ask her what she was wearing. Knowing her, she’d take that the unintended way… lol So our boss opted to text her instead asking her what color shirt she was wearing just for fun. Turned out to be black and white… no green… hmm… must ponder this latest obstacle in the mind control experiment… maybe I should consult the resident evil cat expert… again… hmmm…

Author’s Note: Please be kind if I wrote something wrong. Just let me know and I’ll fix it a.s.a.p…  It’s a bit hard to concentrate when the neighborhood kids are yelling and screaming outside… Anyway, I do believe it’s time to put on some headphones and crank up the music…

Updated: To view the prior post in this series, please refer to “Is It Me or Is That an Error in Your Log?” Next in the series is “Failure? What Failure?”.

Advertisements

5 thoughts on “Ready, Aim, Fire that Trigger!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s