3

For security reasons I need to determine minimal permissions required for a given script or session without having to manually audit activity then work it out by hand.

I'm assuming this can be done by exhaustively tracing all the statements executed, which objects they refer to and which permissions are required to run that statement, but I don't really have the skills or time to build that.

Is there an out of the box solution to this?

I would like to be able to do this for SQL Server 2016 and SQL Azure.

  • 1
    Just SELECT statements or INSERT and UPDATE as well? If they are just SELECT statements, couldn't you create views and give the user rights to the views? – stacylaray Jan 24 '17 at 6:30
  • That is a really good question. I don't think there's a direct way of determining that. @stacylaray gave a good answer, but what i get from your question, is more broad. – Renato Afonso Jan 24 '17 at 10:14
0

I am with Stacy on this one, abstracting the user to make sure they cannot do anything directly and controlling the view/SP code, would work in pretty much all cases. If you run into more complex issues because of a dependancy on elevated permissions (e.g. view serverstate) you may want to look into custom server/application/database roles depending on your need. For checking the permissions you will need for a given task, you could have a look at the permission poster for SQL Server 2016 (https://github.com/microsoft/sql-server-samples/tree/master/samples/features/security/permissions-posters). Azure uses the same permissions, so if it works on-prem it will also work in Azure (according to the applies to statement), but check it to be sure. Azure does have a few less then well documented features here and there. :-) Good luck!

Feel free to mark the awnser or upvote. :-) If you need more info, just let me know.

  • Wow that's a very large poster ... and sounds like an expensive manual auditing task. We built computers to work this stuff out for us didn't we? ;-) – Schneider Jan 24 '17 at 23:29
  • If you want to audit it or to reverse engineer the rights that a user has, you could do both but that's more work then just reviewing your code and checking it off on a poster like this. :-) – MvdMunnik Jan 25 '17 at 0:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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