Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
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. –  Ben Thul Nov 9 '12 at 18:51
    
They are forbidden for this user. Is that what you mean? –  Ducain Nov 9 '12 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. –  Ben Thul Nov 9 '12 at 20:18
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.