0

We have stored procedures in database SQL Server 2016. Say TestDB.

Is there method allowing users to execute only Read-only stored procedures (No write insert/update stored procedures)? The way everything is setup in our legacy system, schemas are not being utilized to separate read access queries. Thank you,

closed as unclear what you're asking by SqlWorldWide, Erik Darling, Shanky, hot2use, LowlyDBA Jan 29 '18 at 19:30

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

The closest you could come is by putting them into a specific role, and granting that role explicit execute access on just those stored procedures. But you would have to identify all of those stored procedures manually.

Even if a procedure were somehow marked in the metadata as "this procedure doesn't write," the permissions system has no way to say "only do things with read-only objects."