SQLPS.com

My good Twitterfriend Jorge Segarra, a.k.a. SQLChicken (Blog | Twitter) came up with a great idea that will hopefully be an awesome learning resource for those wanting to learn SQL Server from scratch, or refresh knowledge on certain topics within the SQL Server realm. It’s the SQL University. Orientation is located here. I am happy to be a part of this effort as a member of the faculty, and look forward to generating content for the syllabus. I hope all SQL newbies enjoy this wonderful idea!.

Ideally, you want to go with Windows Authentication for all your users in SQL Server whenever possible. In the real world, most applications rely on SQL logins. So it happens rather frequently that when the password for a SQL login that is critical to an application is changed, that application breaks. Now, granted, if you are doing things right you have tight controls in place around who can modify passwords, but in reality, this stuff happens all the time.

In this post, I will present you with a script that will create a server-level DDL trigger that will send an email whenever one of a set of deemed critical SQL accounts is modified.

Note that DDL triggers were made available initially in SQL 2005, so this script won’t work on SQL 2000.

IF EXISTS (SELECT *
			FROM sys.server_triggers
			WHERE name = 'trg_notifyPasswordChange')
			DROP TRIGGER trg_notifyPasswordChange ON ALL SERVER;
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TRIGGER trg_notifyPasswordChange
ON ALL SERVER
FOR ALTER_LOGIN
AS
	DECLARE @loginName AS sysname
	DECLARE @serverName AS sysname
	DECLARE @timeOfEvent AS datetime
	DECLARE @loginAffected AS sysname
	DECLARE @bodyOfEmail AS nvarchar(2000)
 
	SELECT @loginName = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(256)')
			, @serverName = EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname')
			, @timeOfEvent = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
			, @loginAffected = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
 
	IF @loginAffected IN ('sa', 'sqlaccount1', 'sqlaccount2') -- List of logins goes here -- modify at your discretion
	BEGIN
		SELECT @bodyOfEmail = @loginName + ', ' + @serverName + ', ' + CONVERT(VARCHAR(20), @timeOfEvent, 100) + ', ' + @loginAffected
 
		-- You will have to modify profile_name and the recipients on the next statement
 
		EXEC msdb.dbo.sp_send_dbmail @profile_name = '<your_dbmail_profile_here>'
										, @recipients = '<your_recipient_list_here>'
										, @subject = 'Critical SQL Login Password Changed!'
										, @body = @bodyOfEmail
	END
GO

Sorry about the formatting — I am still fighting with the formatting WP plug-in. As always, comments are more than welcome. Hope this helps!

UPDATE As Tim Benninghoff commented below, this trigger will fire whenever ANY changes are made to ANY of the critical SQL logins. Please keep in mind that you will likely get false notifications.

Ah, the nuisances of AD and Kerberos. Whenever you change the service account on a SQL instance, you need to make sure that an SPN pointing to the SQL instance is in place for the Service Account. Otherwise, you might get the dreaded “cannot generate sspi context” error. To manage SPNs registered on your service accounts, you will need to use the setspn.exe utility, which is available here.

To see which SPNs are registered with your SQL Service account:

setspn.exe -l <current_service_account>

Unregister the SPN for the old SQL Service account:

setspn.exe -D MSSQLSvc/full.name.to.your.host.here:1433 <old_service_account>

Finally, register the SPN for the new SQL Service account:

setspn.exe -A MSSQLSvc/full.name.to.your.host.here:1433 <new_service_account>

If you use named instances, the syntax for the command will vary slightly. See this post by Jeff Gilbert which has information on syntax to use and other details.

You could also get SSPI errors if there is no registered SPN for the SQL Service on the host itself. See Jeff Gilbert’s post for details on that.

If you want to do transactional replication, all the tables in the publication need to have a primary key. Here’s a quick way to determine which tables have a primary key.

This is the query for SQL 2005 and 2008:

SELECT so.name AS TableName, OBJECTPROPERTY(so.object_id, 'TableHasPrimaryKey') AS HasPrimaryKey
FROM sys.objects so
WHERE type = 'U'

And here is the query for SQL 2000:

SELECT so.name AS TableName, OBJECTPROPERTY(so.id, 'TableHasPrimaryKey') AS HasPrimaryKey
FROM sysobjects so
WHERE type = 'U'