"Loosing your server role rights, such as sysadmin, after activating an activation role seems like a non-issue. "

This is absolutely true. I can't think of a reason why anyone should be accessing an application with an ID that has sysadmin rights in SQL Server. Any ID , and by extension the user of that ID, that has sysadmin rights in SQL Server should have minimal access to data via the application. The sysadmin ID's, particularly on production servers, should only be used to administer SQL, not do anything else. All of our DBA's have separate ID's for sysadmin access, and they use either Citrix or RPC to get to the servers.