Most effective way to determine the minimum set of permissions for user

I have a database that an application connects to using db_owner permissions.

How do I effectively determine the minimum set of requirements actually needed by this user (application) to run without causing service interruption? (ie. without trial and error)

This should be available from the developer who wrote the software. However, on occasions this information is not available - for instance if the software is written by a third party vendor. Most vendors tend to say it needs sa or db_owner which is generally down to poor coding on their part if it turns out to be the case.

If this information is not available from the developer of the software then your best solution would be to run a profiler trace against that user account over a set period of time - this should give you the majority of the objects that it needs access to.

Best practice would be to set up a test environment and put the trace on that - you don't really want to put a trace on a production database unless it is absolutely necessary - and expect significant testing before you can change the production accounts permissions.

I hope this helps you.

StackExchange Database Administrators Q#24507, answer score: 7