I know this is an old thread and may be covered else where but it appeared at the top of the list in google so just in case someone else stumbles across it I thought I would add my experiences.

I have a similar requirement to create users and or logins or adding rights after doing restores from prod to non-prod servers. As suggested above (thanks), I had a look at the trigger on [msdb].[dbo].[restorehistory] but could only get it to work when I manually added a row to the restorehistory table. As it isn't recommended that triggers are put on system tables I didn't look too deeply in to this.

In the end I developed a solution that works for all my servers regardless of version and that is to create an Alert that fires on error 18267 which is the message you see in the errorlog when a database is restored. The alert is then configured to run a SQL job which picks up the database name and user from the restorehistory table and sends me an email to say that a database has been restored.

The script also checks the database name to see if any action is required, e.g. adding users and acts accordingly.

This is great for me as I can now automate tasks that are required after restores and plan to develop it further to check for things like recovery mode as our developers have a habit of leaving restored DB's in Full recovery mode.