Configure Microsoft SQL Server to Report Events to E-mail

For this I had to combine 3 blog articles:

Step 1: Configure Database Mail

Follow the instructions here: http://www.mssqltips.com/sqlservertip/1100/setting-up-database-mail-for-sql-2005/

Summary of steps (with minor variations):

1. Select "Configure Database Mail" by left clicking on "Database Mail"
2. Next
3. "Manage Database Mail accounts and profiles"... Next
4. Create a new profile... Next
5. Configure Profile:
1. Profile name: "SQLAlerts"
2. Add..
1. New Account
1. Account name: "SQLAlerts"
2. E-mail address: mysqlserver@example.com (this will be shown in "sender" e-mail field)
3. Display Name: My SQL Server (this will be shown as display name in "sender" e-mail field)
4. Server Name: smtp.example.com (outgoing SMTP server)
5. ... configure authentication
6. OK
2. OK
3. Next
4. Finish
5. Close

Step 2: Create new Operator and Alert

Corresponding blog post: http://www.mssqltips.com/sqlservertip/1523/how-to-setup-sql-server-alerts-and-email-operator-notifications/

Summary of steps:

1. Under "SQL Server Agent" right click on Operators and select "New Operator..."
1. Name: "DBA"
2. E-mail name: your-email-here@example.com
3. OK
2. Right click on "SQL Server Agent" and choose "Properties"
1. Choose "Alert System" on the left hand side
2. Check "Enable mail profile"
3. If you have more than one mail profile configured, then choose the desired one
4. OK
3. Right click on "SQL Server Agent" and choose "Restart"
4. Under "SQL Server Agent" right click on "Alerts" and select "New Alert..."
1. Under "General" tab:
1. Name: "14 - Insufficient Permission"
2. Severity: "14 - Insufficient Permission"
2. Under "Response" tab:
1. Check "Notify operators"
2. Check E-mail options of those operators that should be notified about an error
3. Under "Options" tab
1. Check "E-mail" checkbox in order to include error description in the mail text

This is the point where your alert sending has been configured, and you're wondering "Why I'm not getting error reports about insufficient permissions?!" Here comes the third step:

Step 3: Alter messages

Corresponding blog article: http://www.simple-talk.com/sql/database-administration/sql-server-alerts-soup-to-nuts/

In order to SQL Server Agent detect errors, they must be written to Application Event Log, but most of them are not. That can be changed using "sp_altermessage" database engine stored procedure. For errors that you would like to receive, execute this query:

sp_altermessage 229, 'WITH_LOG', 'true';

This particular query will enable reporting messages like "The SELECT permission was denied on the object 'mytable', database 'bydb', schema 'dbo'" to application log (and with that - e-mail).

You can explore messages that you could be interested in with this query:

The SELECT permission was denied on the object 'prkad', database 'person', schema 'dbo'