Post reply

Is it possible to grant exec but not select permission if ...

  • August 1, 2012 at 11:49 am

    #279039

    I apologize, if this question has been asked before..

    I did a search, but came up short.

    I have the following scenario:

    I've been asked to create a new db role which has ONLY exec permission.

    It should not be able to run selects ... even C&P'd from an SP.

    I've been able to do this 2 different ways (both with the same problem)

    1) GRANT EXEC TO [ROLE]

    or

    2) GRANT EXEC ON [SPNAME] TO [ROLE]

    The problem I have is when a login associated to the role executes an SP which selects from a table outside the local database.

    i.e.

    SP1 on DB1 selects from table1 on DB1

    SP2 on DB1 selects from table1 on DB1 and joins that result to table2 on DB2

    while SP1 works, SP2 fails with "The SELECT permission was denied on the object table2, database DB2, schema 'dbo'."

    Is this possible without granting select on table2 from DB2?

    If so, how can i accomplish this?

    ANy help will be much appreciated.

    (note.. i'd like to avoid using execute as if possible.. because there are roughly 500+ SP's i'd have to change)

  • August 1, 2012 at 12:05 pm

    #1520446

    This article should help explain what you are experiencing (ownership chaining)

    http://www.mssqltips.com/sqlservertip/1782/understanding-cross-database-ownership-chaining-in-sql-server/

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • August 1, 2012 at 12:23 pm

    #1520460

    A million thanks.

    But, I guess this leads to 1 more question.

    The environment as it is.. has A LOT of cross database stored procs. It looks like issuing

    ALTER DATABASE DB1 SET DB_CHAINING ON; on the majority of our db's is necessary.

    Are their any major security risks involved with doing so... the article didn't really go into detail on them.

  • August 1, 2012 at 4:21 pm

    #1520585

    Giving Permissions through Stored Procedures by Erland Sommarskog

    Look for sentence beginning with "As you might guess."

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply