Post reply

Don't want to set Permissions at table level

  • August 7, 2008 at 2:39 am

    #125501

    One of the database on my server is having 4 users(Windows NT) i have set the persmission EXECUTE to execute the stored procedure not to modify it.

    Now here is the problem, i am using dynamic query in 1 stored proc. and wile executing the SP via Application its saying "SELECT permission doesn't exist on "

    What to do now ?

  • August 7, 2008 at 3:31 am

    #854299

    When using dynamic SQL, the user needs permission on the base tables. The dynamic SQL breaks the ownership chains.

    You may be able to remove the dynamic SQL. I don't know if that's feasible, it depends on what it does. Perhaps you could also look at using the EXECUTE AS clause?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 7, 2008 at 4:12 am

    #854321

    okay let me explain the criteria,

    I have 1 query which contains dynamic where clause. I dn't known which cols will be consider to filer the records, I dn't known the value to be fileter, All comes from 1 table

    e.g

    Colname FilterValue

    A 1

    B 2

    so the where would be A= 1 OR B= 1.. in this sense how can i restrict the dynamic query.

    Abhijit - http://abhijitmore.wordpress.com

Viewing 3 posts - 1 through 2 (of 2 total)

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