Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer
    
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 '11 at 3:55
    
Finally got the trace completed. This program was helpful in the analysis. Thanks again. –  Cory Aug 20 '11 at 22:33
    
Great to hear it was useful. –  Mark Storey-Smith Aug 21 '11 at 0:44

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.

share|improve this answer
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 '11 at 3:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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