Sql-server – Determine minimal permissions required for a given SQL script or session

azure-sql-databasesql serversql-server-2016

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.

Best Answer

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.