Sql-server – How to limit sql user permissions to read only while still allowing stored procedure execution

sql serversql-server-2005

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?

Best Answer

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.

Related Topic