6

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.

Improve this question

2 Answers 2

Reset to default
6

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.

Improve this answer
3
  • I love it! I've never heard of this tool before but it looks like it will greatly reduce the amount of profiler records I would have to sift through. I'll have to start tracing some data tomorrow and try it out.
    – Cory
    Aug 16, 2011 at 3:55
  • Finally got the trace completed. This program was helpful in the analysis. Thanks again.
    – Cory
    Aug 20, 2011 at 22:33
  • Great to hear it was useful.
    – Mark Storey-Smith
    Aug 21, 2011 at 0:44
2

Find out what the logins/users need to do then match that up to server and database roles. Here is a list of Server Roles. Familiarize yourself with those server roles and see the least amount of privileges that the logins need. Do the same with Database Roles.

Improve this answer
1
  • 2
    Thanks for the answer. The hard part is initially figuring out what the logins/users need to do before I can map them to any role. I wouldn't trust any person to be able to accurately tell me what permissions each user needs.
    – Cory
    Aug 16, 2011 at 3:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.