Cleaning up the Lync contacts database

So I found myself in a situation where I needed to programmatically destroy the contact lists of every user in a Microsoft Lync database. Wipe the slate clean, if you will, and let everyone start again. This was the result of management changing their mind about pre-populating everyone's contact lists with a base set of contacts after the fact. It's hard to find information on how to do this in a supported way. In fact, I couldn't find any details at all. What I did find was a mention of calling a stored procedure on the Lync RTC database called ImpDeleteContactGroups2. The ImpDeleteContactGroups2 procedure takes a single argument of the form user@host and does two things.

1. First, it attempts to resolve the user@host input into a numerical ResourceId from the Resource table, and then
2. It calls the ImpDeleteContactGroups procedure with the numeric id

Its the ImpDeleteContactGroups proc that does all the heavy lifting, though in many ways it's a simpler proc than ImpDeleteContactGroups2. As you may have guessed from the name, all ImpDeleteContactGroups actually does is delete contact groups and the association of contacts to contact groups for the user passed in as the original argument. Once it's been run, the user will still have all their contacts associated with their account, but since there are no groups left, the contact list will appear to be empty! But strange things will happen, like Lync will (if it's configured to) populate Outlook with contact details for contacts you can't see any more. All of this leads us to the partner procedure ImpDeleteContacts2, which also has a sibling proc called ImpDeleteContacts. There are no prizes for guessing that ImpDeleteContacts2 takes a user@host style argument, resolves it into a numeric ResourceId, and then calls ImpDeleteContacts to do the actual work of cleaning up contacts. My weapon of choice for this task was PowerShell, but there's nothing stopping you from just firing off queries at the SQL database from inside SQL Server Management Studio. Consider this for an SQL only approach:

USE [rtc]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[ImpDeleteContactGroups2]
  @_Owner = N'some.user@yourdomain.com'

SELECT 'Return Value' = @return_value

EXEC @return_value = [dbo].[ImpDeleteContacts2]
  @_Owner = N'some.user@yourdomain.com'

SELECT 'Return Value' = @return_value

GO

Of course this will suck if you've got lots of users to process. I ended up going for something like this, which drives the Lync command Get-CSUser to provide a complete list of every user registered in your Lync database. NOTE: This will clear all the contact lists and all the groups from every user in your Lync database. I am not responsible if you run this without understanding that.

Import-Module 'C:\Program Files\Common Files\Microsoft Lync Server 2010\Modules\Lync\Lync.psd1' $LyncSQLServerInstance = "YOURSERVER\rtc"; $sqlConnection = new-object System.Data.SqlClient.SqlConnection $sqlConnection.ConnectionString = "server=$LyncSQLServerInstance; Integrated Security=sspi; Database=rtc" $sqlConnection.Open() $sqlCommandDeleteGroups = new-object System.Data.SqlClient.SqlCommand("ImpDeleteContactGroups2", $sqlConnection) $sqlCommandDeleteGroups.CommandTimeout = 120 $sqlCommandDeleteGroups.CommandType = [System.Data.CommandType]::StoredProcedure $sqlCommandDeleteContacts = new-object System.Data.SqlClient.SqlCommand("ImpDeleteContacts2", $sqlConnection) $sqlCommandDeleteContacts.CommandTimeout = 120 $sqlCommandDeleteContacts.CommandType = [System.Data.CommandType]::StoredProcedure Get-CSUser |% { $_.SipAddress -replace "^sip:","" } |% { $sqlCommandDeleteGroups.Parameters.Clear() $sqlCommandDeleteGroups.Parameters.Add("@_Owner", $_) | Out-Null Write-Host "Deleting groups for: " $($sqlCommandDeleteGroups.Parameters[0].Value) $sqlCommandDeleteGroups.ExecuteNonQuery() | Out-Null $sqlCommandDeleteContacts.Parameters.Clear() $sqlCommandDeleteContacts.Parameters.Add("@_Owner", $_) | Out-Null Write-Host "Deleting contacts for: " $($sqlCommandDeleteContacts.Parameters[0].Value) $sqlCommandDeleteContacts.ExecuteNonQuery() | Out-Null }