Database refactoring is worth reading.
I think a sane procedure would follow these steps.
Determine which tables, views, stored procedures, etc., are still being used. Determine which applications are accessing your database. There's more than one way to do that, and the best way depends in part on your dbms.
Auditing can hurt performance. This will take time; faster hardware might be justifiable.
Analyze the results of your audit. Distinguish currently used database objects from seemingly unused database objects. There's no such thing as "definitely unused database objects"; you might have a table that's used only once a year for some kind of accounting process.
Decide which objects to end support for, and deprecate them. Communicate this change both widely and loudly. Remain open to changing your mind when you find you've stepped on someone's toes.
Revoke permissions on formerly deprecated database objects. If you've overlooked an application or user, they'll eventually complain. You might want to approach this in several steps, spread out over time.
Remove the formerly deprecated database objects in such a way you can easily replace them.
Finally, kill the unused database objects. Breathe easier.
Each step takes time. Allow more time than you think you need.