2

Here is my goal: create a login and associated database user with permissions to select (read only), and excecute stored procs that do no updating.

A bit of backstory is that this is SQL 2005, and is a database with a decade+ history and layers of unpleasant things. Hundreds of stored procs, all in schema dbo. I know...

I've got my login and user created, and given the user db_datareader rights. I also had to grant EXECUTE perms in order to run any procs, but my goal is to disallow any proc that does any sort of UPDATE/DELETE actions. Trying to set perms individually on these procs would be a nightmare that I'm not ready to consider.

Is this scenario I describe possible?

Improve this question
3
  • Out of curiosity, how do you intend to handle updates/deletes? If they're straight-up forbidden, you'll need to put triggers in place to be sure they don't happen. Commented Nov 9, 2012 at 18:51
  • They are forbidden for this user. Is that what you mean? Commented Nov 9, 2012 at 19:07
  • Ah... there was a key part missing from your statement: "...my goal is to disallow any proc that does any sort of UPDATE/DELETE actions for this user". I missed the nuance the first time around. Commented Nov 9, 2012 at 20:18

2 Answers 2

Reset to default
1

Once you have logic encapsulated in a stored procedure, permissions on the underlying objects aren't required. One way to accomplish what you're looking to do is with a trigger on the table. Something like:

create trigger tr_del_tbl on tbl after delete as begin if user_name() = 'foobar' and exists (select 1 from deleted) rollback end

I'd also use a role to do this instead of one particular user. That way, if you get another user later that needs similar treatment, you don't have to alter the trigger; you just add them to the role.

Improve this answer
0

There is no permissions based model which will do this unless you are manually assigning rights based on what the stored procedure does. Your only option will be triggers. Keep in mind that will only work if there is no use of EXECUTE AS which would change the way user which is reported by the user_name() function.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.