DDL Trigger Tips

With the growing need to audit activity in your database servers there is a need to figure out the best approach to collect changes on your system. With SQL Server 2005 DDL (Data Definition Language) triggers have been introduced. This type of trigger is different then INSERT, UPDATE and DELETE triggers, this trigger is fired when a change is made using such commands as ALTER, CREATE or DROP.

In a previous tip, SQL Server 2005 SysAdmin Login Auditing, I wrote about how to use a LOGON trigger to capture all logon activity. One of the things you need to aware of is that if you create a logon trigger and there is some bad code you are going to prevent everyone from logging into your SQL Server, even if you try as "sa" or a member of the sysadmin fixed server role. How do I log on to my SQL Server to fix this trigger?

In your development environment if you have locked down permissions for developers, but still need them to execute stored procedures you will need to grant execute rights each time a new stored procedure is generated. In this tip I will show you a way of doing this automatically each time a new stored procedure is created without granting your developers additional permissions.

In our environment we use DDL Triggers both to audit the changes to our systems over time and to provide an easy recovery mechanism for mistakes. (You can read about the basis of our implementation in a previous tip, "DDL Triggers to Track All Database Changes.") The problem is that this audit table can grow quite large if left unchecked, and this can impact both DDL operations in general as well as your attempts to retrieve any useful information from the table after the fact.

Our applications are using a high number of connections and the server is very busy. How do I check whether the application is using connection pooling correctly when connecting to SQL Server? Is there an easy way to see which connections are using connection pooling and which ones are not? In this tip, I will go over how to capture this information for each logon, so you can see which connections are taking advantage of connection pooling.

I want to audit whenever a member of the sysadmin role logs on to my SQL Server. Auditing all successful logins provides that information, however, it logs every connection. The sysadmin logins are being lost amidst all the noise. How can I just track the sysadmin logins to my SQL Server instance?

In a perfect world, only the DBA would have sa privileges, F5 would only ever be hit on purpose, every change would go through rigorous source control procedures, and we would have full backups of all databases every minute. Of course, in reality, we deal with much different circumstances, and we can find ourselves (or overhear someone else) saying, "Oops... how do I fix that?" One of the more common scenarios I've seen involves someone editing a stored procedure multiple times between backups or within some kind of cycle, and then wishing they had version (current - 1) available. It's not in the backup yet, so can't be restored; and the user, of course, has closed his or her window without saving.

Every DBA I've worked with, and I'll include myself in this category, has at one time or another run a sql statement against a database thinking they were in some other environment when they were really in production. It's really too bad there isn't like a 10 second delay after you hit F5 where you can still somehow cancel the statement from running because we all know that it's usually a split second after you hit the key that you realize you made the mistake. This tip will look at a method you can use to add an extra layer of security to help prevent some of these mistakes from happening.

In the Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005 tip, you have seen how you can track DDL activities in your server instance. But how do we store the events captured by these DDL triggers and store it in a table for reporting purposes?

Every DBA I've worked with, and I'll include myself in this category, has at one time or another run a sql statement against a database thinking they were in some other environment when they were really in production. It's really too bad there isn't like a 10 second delay after you hit F5 where you can still somehow cancel the statement from running because we all know that it's usually a split second after you hit the key that you realize you made the mistake. This tip will look at a method you can use to add an extra layer of security to help prevent some of these mistakes from happening.

In a perfect world, only the DBA would have sa privileges, F5 would only ever be hit on purpose, every change would go through rigorous source control procedures, and we would have full backups of all databases every minute. Of course, in reality, we deal with much different circumstances, and we can find ourselves (or overhear someone else) saying, "Oops... how do I fix that?" One of the more common scenarios I've seen involves someone editing a stored procedure multiple times between backups or within some kind of cycle, and then wishing they had version (current - 1) available. It's not in the backup yet, so can't be restored; and the user, of course, has closed his or her window without saving.

Our applications are using a high number of connections and the server is very busy. How do I check whether the application is using connection pooling correctly when connecting to SQL Server? Is there an easy way to see which connections are using connection pooling and which ones are not? In this tip, I will go over how to capture this information for each logon, so you can see which connections are taking advantage of connection pooling.

With the growing need to audit activity in your database servers there is a need to figure out the best approach to collect changes on your system. With SQL Server 2005 DDL (Data Definition Language) triggers have been introduced. This type of trigger is different then INSERT, UPDATE and DELETE triggers, this trigger is fired when a change is made using such commands as ALTER, CREATE or DROP.

I want to audit whenever a member of the sysadmin role logs on to my SQL Server. Auditing all successful logins provides that information, however, it logs every connection. The sysadmin logins are being lost amidst all the noise. How can I just track the sysadmin logins to my SQL Server instance?

In your development environment if you have locked down permissions for developers, but still need them to execute stored procedures you will need to grant execute rights each time a new stored procedure is generated. In this tip I will show you a way of doing this automatically each time a new stored procedure is created without granting your developers additional permissions.

In our environment we use DDL Triggers both to audit the changes to our systems over time and to provide an easy recovery mechanism for mistakes. (You can read about the basis of our implementation in a previous tip, "DDL Triggers to Track All Database Changes.") The problem is that this audit table can grow quite large if left unchecked, and this can impact both DDL operations in general as well as your attempts to retrieve any useful information from the table after the fact.

In a previous tip, SQL Server 2005 SysAdmin Login Auditing, I wrote about how to use a LOGON trigger to capture all logon activity. One of the things you need to aware of is that if you create a logon trigger and there is some bad code you are going to prevent everyone from logging into your SQL Server, even if you try as "sa" or a member of the sysadmin fixed server role. How do I log on to my SQL Server to fix this trigger?

In the Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005 tip, you have seen how you can track DDL activities in your server instance. But how do we store the events captured by these DDL triggers and store it in a table for reporting purposes?

Every DBA I've worked with, and I'll include myself in this category, has at one time or another run a sql statement against a database thinking they were in some other environment when they were really in production. It's really too bad there isn't like a 10 second delay after you hit F5 where you can still somehow cancel the statement from running because we all know that it's usually a split second after you hit the key that you realize you made the mistake. This tip will look at a method you can use to add an extra layer of security to help prevent some of these mistakes from happening.

In our environment we use DDL Triggers both to audit the changes to our systems over time and to provide an easy recovery mechanism for mistakes. (You can read about the basis of our implementation in a previous tip, "DDL Triggers to Track All Database Changes.") The problem is that this audit table can grow quite large if left unchecked, and this can impact both DDL operations in general as well as your attempts to retrieve any useful information from the table after the fact.

In a perfect world, only the DBA would have sa privileges, F5 would only ever be hit on purpose, every change would go through rigorous source control procedures, and we would have full backups of all databases every minute. Of course, in reality, we deal with much different circumstances, and we can find ourselves (or overhear someone else) saying, "Oops... how do I fix that?" One of the more common scenarios I've seen involves someone editing a stored procedure multiple times between backups or within some kind of cycle, and then wishing they had version (current - 1) available. It's not in the backup yet, so can't be restored; and the user, of course, has closed his or her window without saving.

In your development environment if you have locked down permissions for developers, but still need them to execute stored procedures you will need to grant execute rights each time a new stored procedure is generated. In this tip I will show you a way of doing this automatically each time a new stored procedure is created without granting your developers additional permissions.

Our applications are using a high number of connections and the server is very busy. How do I check whether the application is using connection pooling correctly when connecting to SQL Server? Is there an easy way to see which connections are using connection pooling and which ones are not? In this tip, I will go over how to capture this information for each logon, so you can see which connections are taking advantage of connection pooling.

In a previous tip, SQL Server 2005 SysAdmin Login Auditing, I wrote about how to use a LOGON trigger to capture all logon activity. One of the things you need to aware of is that if you create a logon trigger and there is some bad code you are going to prevent everyone from logging into your SQL Server, even if you try as "sa" or a member of the sysadmin fixed server role. How do I log on to my SQL Server to fix this trigger?

I want to audit whenever a member of the sysadmin role logs on to my SQL Server. Auditing all successful logins provides that information, however, it logs every connection. The sysadmin logins are being lost amidst all the noise. How can I just track the sysadmin logins to my SQL Server instance?

In the Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005 tip, you have seen how you can track DDL activities in your server instance. But how do we store the events captured by these DDL triggers and store it in a table for reporting purposes?

With the growing need to audit activity in your database servers there is a need to figure out the best approach to collect changes on your system. With SQL Server 2005 DDL (Data Definition Language) triggers have been introduced. This type of trigger is different then INSERT, UPDATE and DELETE triggers, this trigger is fired when a change is made using such commands as ALTER, CREATE or DROP.