Tell me more ×
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.
up vote 5 down vote favorite

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)

share|improve this question

2 Answers

up vote 7 down vote accepted

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.

share|improve this answer
That's exactly what I had in mind. It confirms my suspicions, so it does help, thank you! I'll keep the question open to see if anyone suggests another angle to this. Thanks again! – ivanmp Sep 18 '12 at 23:07
4  
Audit Database Object Access Event is a good one to use in Profiler for this. I have used it to help me get an idea of what objects an application used and what permissions. There is also one similar that will capture failed attempts to use an object. – Shawn Melton Sep 19 '12 at 1:13
4  
A side note as well since you have SQL 2008 R2, you might look at SQL Security Audits. It uses Extended Events and is a lot less intrusive than running Profiler for an extended period of time. msdn.microsoft.com/en-us/library/cc280386.aspx – Shawn Melton Sep 19 '12 at 1:15
@ShawnMelton nice! This is definitely worth a look. Thanks :) – ivanmp Sep 19 '12 at 10:54
up vote 5 down vote

It all boils down to one simple thing: do you have access to the source code of the application?

If you do (and presumably buy-in to make these changes; also, technically, reverse-engineering falls into this category, but... you didn't hear that from me), it's simply a matter of searching, testing, and documentation. Smooth sailing, right? :)

If you don't, there are two cases:

  1. The application is currently under license and/or the vendor/owner doesn't want you meddling. Making this kind of change is a definite no-no. There are rare cases where database-level changes are okay, but this certainly isn't one of them.

  2. The application is out of license or the vendor/owner doesn't care what you do with it. In this case, there is no choice but to determine which permissions are required based on database queries made by the application. Technically, this isn't guessing, but you'll only ever find the permissions that are needed based on what has already occurred, not what is needed in the future. This comes down to thorough tracing (either Profiler or Extended Events) and testing.

share|improve this answer

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.