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

Question

If I have a SQL set (i.e. a script containing arbitrary SQL statements), is there a way to find out what minimal permissions are needed to execute SQL?

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

A bit of background:

As part of the application, I have a set of update scripts (which modify the data in the tables, as well as modify the schema) that will run with the client database at the end. I want to analyze these update scenarios for any potential resolution problems before I deploy them, because the client that runs them may have limited login to SQL Server. The types of events that occur in these scenarios usually include / delete / modify tables / columns / indexes, but I also choose from views of the information schema and system tables.

EDIT:

I have error handling to handle these cases where the user does not have the correct permissions to update. I am also currently checking that the user performing the update is db_owner , but I am more interested in things outside the updated database. For example, scripts often use system tables to obtain information about the database schema to decide whether to perform a specific action - I would like to know what permissions are needed to access these tables. Another example is the inclusion of page compression - does the user need certain permissions to do this?

I want to check the update scripts before deploying them, since it is much better to understand that you need certain permissions in advance, and not just with an update error.

Answer 1

I do not think such a team exists.

I would recommend that the update account be set to db_owner and ask them to run the script. Be more simple. Or send the role to your db for this purpose.

Perhaps you could create a pre-execute phase that tries to execute some commands and then drops them back. If you didn’t have access, this would trigger some errors. Pretty shreds though.

Answer 2

I was thinking the same thing. It would be great if there was some option in the SQL profiler that would allow you to keep track of which permissions were used. Thus, you can simply provide the system administrator with a login, after which the user / application will go through it and delete all permissions that have not been used.

How about ... If you traced the profiler and saved it as SQL Script, then something worked through each statement in the trace to determine the necessary permissions?

As a by-product, you get a convenient Script that (theoretically) will function as a test of all the functions that can be used to monitor the state of the application.