Personally, in almost all situations, I wouldn't use it. My own feeling is if you use a database-neutral technology you shouldn't then restrict the set of databases you can use it against by relying on the database to generate primary keys. The only time I would use it is if the schema already exists, uses identity columns and cannot be changed.
Instead, I'd use some other generation strategy that is implemented by the application using a well understood algorithm seeded from the database, so always use ASSIGNED for the generaiton type.
According to the Hibernate documentation DB2, MySQL, MS SQL Server, Sybase and HypersonicSQL support it, and I can't expand on that list.