SharePoint: deleting a SSP leaves a running SQL agent job

Hello,

On one of MS SQL Server database backend machines, I was getting a lot of windows event log entries stating that there was a problem for our SQL Server account with accessing one of the databases:

Login failed for user 'DOMAINsqluser’. Reason: Failed to open the explicitly specified database. [CLIENT: x.x.x.x]

Investigating the corresponding SQL Server instance logs, further details of the issue followed:

[298] SQLServer Error: 18456, Login failed for user 'DOMAINsqluser’. [SQLSTATE 28000]

[298] SQLServer Error: 4060, Cannot open database "SSP_XYZ" requested by the login. The login failed. [SQLSTATE 42000]

First idea was of course checking the permissions of the user within that database, but then there came the weird thing – a database with such name did not exist. Another approach was that maybe some old web application has been forgotten and still uses explicitly the DB name (e.g. in web.config). Not the cause either.

Finally, since the database name contained ‘SSP’, it had most probably something to do with a Shared Services Provider database. The current one we have has a different name, so the name occurring in the error logs referred to a non-existing SSP. We were able to find out that such SSP has been created and deleted a while ago. The corresponding database has been also removed from the SQL-Server, but one oddment remained there: a SQL Agent job for deleting expired sessions. The agent tried to connect to that DB every minute, and encountered the error mentioned above.

You can find the jobs either directly in the table ‘msdb.dbo.sysjobs’, or within the object explorer, under the “SQL Server Agent” node: