<p>Is is possible to write MS SQL Server add-in? I'm thinking of some application integrated with database server available form SQL Server Enterprise Manager.</p>

You can include a lot into MS SQL server 2005 and 2008 database. Including .Net code. 
But there is no real way to extend (create plugin for) the Management Studio. (Formerly Enterprise Manager.)
However there are database management addins for Visual Studio (especially in Team System). Maybe there you can implement what you need.