0

I have a db user that has only db_reader permission. (He has SELECT on everything).

I have 100 stored procedures. I want him to have the ability to execute only the SELECT SP and not DML. Unfortunately, granting him 'execute' on all - allows him to also perform DML (INSERT/UPDATE/DELETE).

Is there a way around it?

(I don't want to run on all SP and give him specific grants on specific functions because I want it to also support future changes and future SPs)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Faruz
  • 9,909
  • 10
  • 48
  • 66

3 Answers3

1
  • Create a role in the database

  • use the code below to add the role to each of the "select" stored procedures

    use [YourDBNameHere] GO GRANT EXECUTE ON [TheSchemaNameHere].[YourSPNameHere] TO [YourRoleNameHere] GO

  • Add the use to the role

  • remove user permission of "select from everything"

RC_Cleland
  • 2,274
  • 14
  • 16
  • Then I have to hand-pick the SPs. Which is a pain for future development. – Faruz Jan 03 '11 at 10:55
  • @Faruz by adding the group you only have to pick the sp one time. Add the users to the group. Never work with users only groupts, but you know that – RC_Cleland Jan 06 '11 at 22:48
  • Thank you for your answer. Problem is that SPs change every build, there are new ones, updated ones and rarely even removed ones. So I need to maintain this list every build which is a pain. – Faruz Jan 09 '11 at 05:16
1

There is no clever way, no matter if it's a pain as per other comments

  • permission separately (in other answers)
  • use schemas (in other answers)
  • use a trigger on all your tables

And DENY or db_denydatareader won't work at the table level

A stored proc can run any code: there is no way to distinguish a select or a DML proc.

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
0

Permissions in SQL Server can be applied per-schema. Put your SELECT procs into a separate schema and allow EXEC permissions on it.