Is there a tool to check the minimum privileges required for a set of sql?

Question

If I have a set of SQL (ie. a script containing arbitrary SQL statements), is there any way of finding out what the minimum permissions are required to execute the SQL?

(I'm thinking of something similar to the "Permissions required by the application" area in Visual Studio when viewing the Security tab on the project properties page of a WinForms application.)

A bit of background:

As part of an application, I have a set of upgrade scripts (which alter the data in tables as well as altering the schema) which will get run against a customer's database at their end. I want to analyse these upgrade scripts for any potential permission problems before I deploy them, as the customer running them may have a restricted login to SQL Server. The types of things that happen in these scripts are typically adding/removing/altering tables/columns/indices, but I also do select from the informational schema views and the system tables.

EDIT:

I have error handling in place to cope with those occasions when a user doesn't have the correct permissions to do an upgrade. I am also currently checking that the user running the upgrade is db_owner, but I'm more concerned with things outside the database being upgraded. For example, scripts quite often use the system tables to get information about the database schema to decide whether to do a specific action - I'd like to know what permissions are needed to access these tables. Another example is turning on page compression - does the user need specific permissions to do this?

I'd like to do the check on the upgrade scripts before deploying them, as it is a much better user experience to be aware that you need certain permissions beforehand, rather than the upgrade just failing with an error.

Answer 1

I don't think such a command exists.

I would recommend the upgrade account be set to db_owner and have them run the script. Keep it simple. Or ship a role in your db for this purpose.

Maybe you could create a 'pre-execute' phase which tries to execute some commands and then rolls them back. It would throw some errors if you didn't have access. Quite a kludge though.