In SQL Server 2005 how would I determine the minimal permissions a user requires?

I'm the accidental DBA of my group and I've inherited a database that follows a number of bad practices. Currently every login is a member of the sysadmin group.

What is the easiest way to determine the minimal amount of privileges needed for each account? I know I can use SQL Server Profiler to log all the SQL commands executed but it will return a bunch of information. I can not think of an easy way to summarize all that data.

Any help would be appreciated.

If you want to cut the noise out of a Profiler trace, ClearTrace is invaluable. ClearTrace “normalizes” queries, removing parameters such that:

SELECT x FROM y WHERE z = 1
SELECT x FROM y WHERE z = 2

are both reported as

SELECT x FROM y WHERE z = #

If you capture sufficient activity from the database and order the ClearTrace report by execution count, you’ll have a better idea of the common/typical query patterns and from this the permissions required. You can then investigate the non-typical activity and establish if these require a different set of permissions and/or are generated by a subset of the user base.

StackExchange Database Administrators Q#4656, answer score: 6