Handling Deleted Users Who own SQL Reporting Subscription

Posted by beakersoft | Posted in SQL Reporting Services | Posted on 01-06-2011


I’ve had an issue this week with some SQL reporting services subscriptions. A user contacted me saying her subscription reports were all failing. At first I assumed there was a problem with the user the report was running as, but it turned out to be something else.

When I tried to change and save the settings on one of the reports, I was presented with this error:

The user or group name ‘Dom\User.Name’ is not recognized. (rsUnknownUserName)

Now, it turned out that the user that had originally create this report subscription had recently been deleted out of the active directory, and there was a lot of reports that this user had created. I had a poke around on the web interface and in the management tools, but i couldn’t see anywhere to change the owner of the subscription.

So, as i’ve had to do in the past I started having a look in the reporting service database. I looked first in the Subscriptions table, and there was an OwnerID field, when I linked this table back to the users table I found it was indeed linked to the old user that had been removed. So, I found a valid user in the table, and ran this SQL to update the Subscriptions table pointing to this user:

UPDATE dbo.Subscriptions SET OwnerID = ‘xxx-xxx-xxx-xxx-New-User’ WHERE OwnerID = ‘xxx-xxx-xxx-xxx-Old-User’

When I opened up the subscription again, the owner was now valid, and I could save the report without the error message. I’m glad i did’nt have to go back to the user and tell them they needed to re-create all the subscriptions!

Comments (1)

Very in formative.Thank you.

Write a comment

Comments links could be nofollow free.