I have SMO code which copies tables from one database to another. It runs fine in a desktop app. The same exact code put in a service, gives an error. The error is really obscure and doesn't hint about permissions.
I was wondering if the SMO objects need any kind of security to be used. Security context? Windows credentials.. etc? I am not talking about SQL Server security but security surrounding the use of the SMO methods.
Are you using integrated security with your connection? If so, make sure the credentials that the service is running under have necessary access.
Make sure your service account (the account that your windows service is running as) has "Act as Part of Operating System" Policy.
You need full trust. You may have problems running this on certain hosted ASP.NET accounts, apps downloaded from the internet, or from shared drives, where CAS kicks in and gives the code something less than full trust and certain APIs won't be callable.